VistaDB 5
TSQL Proc Creation

Stored Procedure Creation

Stored procedures are created by executing the CREATE PROCEDURE command.

CREATE PROCEDURE
Copy Code
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

Not all SQL Server syntax is supported. See the related topics entry below for more information.
See Also