Creating a Stored Proc

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.

Executing the Stored Procedure from SQL

declare @outvar MONEY;
declare @total MONEY;
set @total = 100.00;
exec calcsalestax @total, @outvar out;
select @outvar;

Executing Stored Procedures from Code

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);
}