How to pass output parameter to Stored procedure in SQL Server

Hello friends, today we will see how to pass output parameters to stored procedures in SQL Server. Lets create a table first, using below SQL script.

CREATE TABLE [Emp](

      [Eid] [int] NULL,

      [Name] [varchar](15) NULL

) ON [PRIMARY]

Now we will insert few rows into this table.

Insert into Emp values(107,'Akash Rajput')

Insert into Emp values(108,'Vudushi Pandey')

Insert into Emp values(109,'Shivika Garg')

Insert into Emp values(110,'Pranav Kumar')

Insert into Emp values(115,'Mohan Sharma')

Lets create a stored procedure with output parameter using below SQL Script.

Alter procedure Usp_Insert_Select_Emp(@Eid int,@Name varchar(20),@ErrorCode int out)

as

begin

      begin try

            Insert into Emp Values(@Eid,@Name)

            select * from Emp

      end try

      begin catch

            select @Errorcode=ERROR_NUMBER()

      end catch

end

In the above script of stored procedure, we can see that we have defined one output parameter @Errorcode. To define output parameter, we just need to put “Out” or “Output” keyword after declaring the parameter (@Errorcode int Out).

In the body of stored procedure, there are two blocks which are following.

Try Block:- In try block we have written insert statement and passing the @Eid and @Name variables into the insert statement and after inserting the values, we have written select statement.

Catch Block:- In catch block we are assigning the ERROR_NUMBER()value to variable @Errorcode. ERROR_NUMBER() is system defined function which returns error number, if any error has occurred.

Lets execute this stored procedure using below SQL Statement and see the results.

declare @Err int

Exec Usp_Insert_Select_Emp 116,'Priyanka Verma', @Err out

print @Err

We can see in the above screenshot that one row has inserted successfully in employee table. Lets click on the messages tab(encircled by red color) to see if any error code has been return by the stored procedure or not.

We can see in the above screenshot that messages tab has no error code. Lets execute the stored procedure one more time with different parameters.

declare @Err int

Exec Usp_Insert_Select_Emp 116,'Vinod Kumar Yadav', @Err out

print @Err

This time we received the error code(8152),as we can see that in the @Name parameter we have passed “Vinod Kumar Yadav”, which is having length( 17) while “Emp” table has length(15) for “Name” column. Hence an error occurred and control went to Catch block and we received the error code.

Note:- Generally we use output parameters for error handling but there can be other business scenarios where we can use these output parameters.

I hope you will find this article useful.