How to Create a Stored Procedure with OUTPUT Parameters in SQL

To create an SP with output parameter, we use the keywords OUT or OUTPUT.

Example: Use this below Database

Code:

USE TestDataBase

Code: Create a table to understand stored procedure

CREATE TABLE tbl_customer
(
customerID INT PRIMARY KEY IDENTITY(100000000,1),
customerSSNId INT,
customerName VARCHAR(100),
customerAge int,
customerAddressLine1 VARCHAR(100),
customerAddressLine2 VARCHAR(100),
customerCityID VARCHAR(50),
customerStateID VARCHAR(50) 
)

Code: Insert a record inside the table

INSERT INTO tbl_customer VALUES (12345678, 'Rumman Ansari', 23, 'Kolkata', 'Rajarhat', '12', '29')

Code: See the inserted record

Select * from tbl_customer

Example 4: Stored Procedure With OUT Parameters

Code: Create another store procedure which will return a parameter as output

/* Insert into Customer table with data records
Stored Procedure Example - 4 | Stored Procedure With Out Parameters
*/

CREATE PROCEDURE sp_insertIntoTable
 (   
@customerSSNId INT,
@customerName VARCHAR(100),
@customerAge int,
@customerAddressLine1 VARCHAR(100),
@customerAddressLine2 VARCHAR(100),
@customerCityID VARCHAR(50),
@customerStateID VARCHAR(50),
@customerIDOut INT OUT
 )
 AS
 BEGIN

 INSERT INTO tbl_customer VALUES 
 ( 
	@customerSSNId,
	@customerName,
	@customerAge,
	@customerAddressLine1,
	@customerAddressLine2,	
	@customerCityID,
	@customerStateID
 )
	SET @customerIDOut = @@IDENTITY
END

Example 5: Stored Procedure With OUTPUT Parameters

If you declare a parameter as OUTPUT, it acts as Both Input and OUTPUT

Code: Execute the above stored procedure

-- Execute
DECLARE @customerIDOut1 INT
EXEC sp_insertIntoTable 12345678, 'Ram Ansari', 23, 'Kolkata', 'Rajarhat', '12', '29', @customerIDOut1 OUT
PRINT @customerIDOut1 

-- See table content
SELECT * FROM tbl_customer

Code: Create a stored procedure which will take a input parameter as well as give a output parameter

/* Insert into Customer table with data records
Stored Procedure Example - 5 | Stored Procedure With Output Parameters
*/ 

ALTER PROCEDURE sp_insertIntoTable1
 (   
@customerSSNId INT OUTPUT,
@customerName VARCHAR(100),
@customerAge int,
@customerAddressLine1 VARCHAR(100),
@customerAddressLine2 VARCHAR(100),
@customerCityID VARCHAR(50),
@customerStateID VARCHAR(50),
@customerIDOut INT OUT
 )
 AS
 BEGIN

 INSERT INTO tbl_customer VALUES 
 ( 
	@customerSSNId,
	@customerName,
	@customerAge,
	@customerAddressLine1,
	@customerAddressLine2,	
	@customerCityID,
	@customerStateID
 )
	SET @customerIDOut = @@IDENTITY
	SET @customerSSNId = @customerSSNId+1
END

Code: Execute the above stored procedure

-- Execute
DECLARE @customerIDOut1 INT,  @customerSSNId1 INT = 12345678
EXEC sp_insertIntoTable1 @customerSSNId1 OUTPUT, 'Ram Ansari', 23, 'Kolkata', 'Rajarhat', '12', '29', @customerIDOut1 OUT
PRINT @customerIDOut1
PRINT @customerSSNId1

 
-- See the table content
SELECT * FROM tbl_customer

You can pass parameters in any order, when you use the parameter names.

The following system stored procedures, are extremely useful when working procedures.

sp_help SP_Name : View the information about the stored procedure, like parameter names, their datatypes etc. sp_help can be used with any database object, like tables, views, SP's, triggers etc. Alternatively, you can also press ALT+F1, when the name of the object is highlighted.

sp_helptext SP_Name : View the Text of the stored procedure

sp_depends SP_Name : View the dependencies of the stored procedure. This system SP is very useful, especially if you want to check, if there are any stored procedures that are referencing a table that you are abput to drop. sp_depends can also be used with other database objects like table etc.

Note: All parameter and variable names in SQL server, need to have the @symbol.