SQL Server Stored Procedure with Return statement

In previous posts, we learnt about stored procedure and its types such as procedure with input and output parameters. In this post, we will see how a stored procedure with return statement is different from stored procedure with output parameter.

A stored procedure with return statement returns an integer value indicating the status of execution of the stored procedure. It returns zero on success, and non-zero on failure. Look at the following example in this regard.

use AppliedkDb
go

create procedure uspGetTotalNoOfEmployees
As
begin
return (select count(*) from Employees)
end

Now execute the stored procedure. The procedure returns value which is stored in the declared variable. This is shown in the following code.

Declare @num_employees as int
EXEC @num_employees = uspGetTotalNoOfEmployees
PRINT @num_employees

Note that the data returned by the stored procedure is stored in variable by executing the procedure. We use EXEC not SET statement for this.

We can get the same result by using OUTPUT parameter as given below.

use AppliedkDb
go

create procedure usp_GetTotalNoOfEmployees
@total_employees as int output
As
begin
set @total_employees = (select count(*) from Employees)
end

Now run the procedure.

Declare @num_employees as int
EXEC usp_GetTotalNoOfEmployees @num_employees output
print @num_employees

Limitations of Return Statement

1. It returns a single value.
2. Return statement always returns an integer value.
3. It shows the status of success of execution of stored procedure.