This blog is part of series The TSQL Classes: Stored Procedures.
Introduction:
To put simply, a SQL Server stored procedures is set of queries put together primarily to reuse a complex/iterative business logic. However there are other benefits attached to it as well; which we will see as the class progresses.
Creating a stored procedure
Syntax for creating stored procedure is shown above. At first you name your procedure and specify input and output parameters, if required. After this specify the procedure’s body. Let’s now look how to execute a stored procedure.
The execution is very simple as shown in above snapshot. Specify a stored procedure name and the parameters. In case a procedure has an output parameter, then declare a local variable and pass it as shown in above image. The result of the output parameter will be passed to that local variable.
The Return statement
It returns a status value to a calling procedure or a batch indicating success of failure of a stored procedure. Every stored procedure returns a value of 0 by default. Another thing specific to return statement is that none of the code below it is executed. A procedure ends as its encounters a return statement. This feature can be used in debugging a stored procedure which we’ll see in future classes. Let’s see how to get the value returned by a stored procedure using return statement.
Return Vs Output
| Return Statement | Output Parameter |
| Returns only a single integer value. | Returns multiple values with different data types. |
| Purpose is to indicate a success or failure of a stored procedure | Purpose is to output values. |
| Terminates a stored procedure and none of the code after return statement is executed. | It doesn’t. |
Summary:
In this class I introduced stored procedures. I gave syntax for createing and executing a stored procedure with input and output paramters. I then explained about return statement in a stored procedure and how does it differs from the output parameters.
Like us on FaceBook | Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook
