First create the stored procedure.
CREATE PROCEDURE CalcSalesTax @SalesTotal MONEY, @OrderTotal MONEY OUT AS BEGIN DECLARE @total MONEY; -- You would probably look this up in a table DECLARE @taxrate INT; SET @taxrate = 6; SET @total = @SalesTotal + (@SalesTotal/100*@taxrate); SELECT @OrderTotal = @total; END
If you don't want to use an out value, you could also RETURN a value. But only integers may be returned from a stored procedure.
declare @outvar MONEY; declare @total MONEY; set @total = 100.00; exec calcsalestax @total, @outvar out; select @outvar;
The code to execute this procedure is the same as SQL Server stored procs. You must declare each param, and the direction the param is to be used for in the procedure.
// Execute a stored procedure using( VistaDB.Provider.VistaDBCommand command = new VistaDB.Provider.VistaDBCommand("CalcSalesTax", connection) ) { command.CommandType = CommandType.StoredProcedure; VistaDBParameter param = command.Parameters.AddWithValue("@SalesTotal", 100.00); param.Direction = ParameterDirection.Input; param.DbType = System.Data.DbType.Currency; VistaDBParameter outparam = command.Parameters.Add("@OrderTotal", VistaDBType.Money); outparam.Direction = ParameterDirection.Output; command.ExecuteNonQuery(); Assert.AreEqual(outparam.Value, 106.00); }