Stored Procedure Creation

Stored procedures are created by executing the CREATE PROCEDURE command.

CREATE { PROC | PROCEDURE } procedure_name [ { @parameter data_type } [ = default ] [ OUT | OUTPUT ] ] [ ,...n ]
AS { [;][ ...n ] | } [;] ::= ::=
{ [ BEGIN ] statements [ END ] }

Examples

This is a simple proc to return 100. In this example the get100 has no parameters, and returns a single Integer value. SQL Server only allows Integer return values from stored procedures. VistaDB currently allows any return type, but this may change for compatibility reasons.

CREATE PROCEDURE Get100
AS
BEGIN 
     DECLARE @ProcReturn Integer; 
     set @ProcReturn = 100; 
     return @ProcReturn;
END

To capture the return value in SQL you would need to declare an integer variable and use the following EXEC syntax.

DECLARE @myreturn integer;
EXEC @myreturn = get100;
SELECT @myreturn;

Remarks