SQL Server – Stored Procedure with Output Parameters

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

-- 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.