VistaDB 6
VistaDB / Developer's Guide / SQL Reference / Stored Procedures and User-Defined Functions / Executing stored procedures
In This Topic
    Executing stored procedures
    In This Topic
    Stored procedures are executed using the EXEC or EXECUTE commands.

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