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