SQL Stored Procedure Parameters

SQL Server Stored-Procedures can return data in 4 forms:
1. Return Code: which are always an integer value.
2. OUTPUT Parameter: which can return either data (such as an integer or character value) or a cursor variable (cursors are result sets that can be retrieved one row at a time).
3. A result set for each SELECT statement contained in the stored procedure or any other stored procedures called by the stored procedure.
4. A global cursor that can be referenced outside the stored procedure.

In SQL Server, there are two ways with we can pass parameters to procedures.

• INPut parameter
• OUTPut parameter

INPut parameter
These types of parameters are used to send values to stored procedures.
OUTPut parameter
These types of parameters are used to get values from stored procedures. This is similar to a return type in functions.
In this post we will try to understand how stored procedure returns the parameter
Example

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

-- Create a table CREATE TABLE myTable (     ID INT Identity(1,1),     Name VARCHAR(20) ) -- Now Create a Stored Procedure with the OUTPUT  parameter CREATE PROCEDURE my_sp (     @name VARCHAR(20),     @id_out INT OUTPUT ) AS BEGIN INSERT INTO myTable VALUES (@name) SELECT  @id_out = Scope_Identity () END GO

Stored Procedure with Output Parameters

Similarly, for the second row

Stored Procedure with Output Parameters

Stored Procedure with Output Parameters

Similarly you can get values from stored procedures as you need or required. Hope this post helps you to understand the OUTPut parameters.

Input parameters are those parameters that you pass to the stored procedure

which the SP uses to retrieve records or do some manipulation. 

Output parameters are those parameters that you want the stored procedure

to return to the application that is calling it.

Basically you use output parameters if you want to

return a single value to the calling application.

If you intend to return multiple values or multiple rows,

you can use a record set instead (by calling a SELECT statement).