SQL Server stored procedure return value – Complete guide

In this SQL Server tutorial, we will discuss How to return values from a stored procedure. We will also discuss how we can return some specific values from a stored procedure. The complete list of topics discussed in this article is listed below.

• SQL Server stored procedure return value
• SQL Server stored procedure return value -6
• SQL Server stored procedure return value to variable
• SQL Server stored procedure return value 0
• SQL Server stored procedure return value –1
• SQL Server stored procedure return value –4
• SQL Server stored procedure return value varchar
• SQL Server stored procedure return more than one value
• SQL Server stored procedure return identity value
• SQL Server stored procedure returns negative value
• SQL Server stored procedure return value if exists
• SQL Server stored procedure return boolean value
• SQL Server stored procedure return value null
• SQL Server stored procedure return resultset
• SQL Server stored procedure return multiple resultsets
• SQL Server stored procedure return table
• SQL Server stored procedure return value datetime
• SQL Server stored procedure transaction return value
• SQL Server stored procedure return two values
• SQL Server stored procedure return value rows affected
• SQL Server stored procedure return value output parameter
• SQL Server stored procedure return value vs output parameter
• SQL Server stored procedure return value best practices

SQL Server stored procedure return value

There are mainly 3 methods through which we can return some values from a sql stored procedure to the caller of the procedure in sql server 2019. All the 3 methods are listed below.

1. Return Codes
2. Result sets
3. Output parameters

Return Codes in stored procedure

• Whenever we execute a stored procedure in SQL Server, it returns an integer value to the caller. These integer values are used to represent the execution status of a procedure.
• A zero result code represents the successful execution, and a non-zero code represents some failure in execution.
• We can also specify the return codes by using the RETURN statement.
• Overall, Return codes are used to determine the success or failure of a stored procedure. These are the integer values that can be used to determine any state of the stored procedure. By default, a stored procedure returns a 0 value if the execution is successful. You can also send your custom integer values to send any other information about the stored procedure.

Result sets

• A result set is a group of records or rows returned from a query.
• We can also return result sets from a procedure to the caller directly using including the SELECT statement in the procedure body.
• We can also include multiple SELECT statements in a single procedure to return multiple result sets directly to the caller.
• In short, we can return resultsets from a stored procedure using a Select statement inside a stored procedure. When you use a Select statement inside a stored procedure that returns a resultset, this resultset is passed to the calling program or stored procedure. 

Output parameter in stored procedure

• An output parameter in a stored procedure is used to send some value from the procedure back to the caller or client. A stored procedure can have multiple output parameter.
• But, an output parameter will not return the value directly to the caller. For this, first, we have to declare a variable that can hold the value of the output parameter.
• And while execution, we have to specify the declared as an output parameter.
• After execution, we can use the variable to get the value.
• You can return almost any type of data using the output parameter. For using an output parameter, you must specify the OUTPUT keyword at the time of declaration. You can return data like numbers, strings, table-valued variables, etc using the output parameters.

We will discuss the implementation of all these methods further in this article. We will see how you can return various types of data with various techniques.

Read Try catch in SQL Server stored procedure

SQL Server stored procedure return value data type

You might be talking about the data type of return codes in SQL Server stored procedure. The data type of a return code is always an integer. If you want to return other types of data, you have to use the output parameters. You cannot use the return codes in that case.

SQL Server stored procedure default return value

If you execute a stored procedure without returning any value, the stored procedure always returns a default value. If the execution of the stored procedure is successful, it returns 0 as the default return value. If the execution fails or stops because of an error, a non-zero value is returned as the default return value.

SQL Server stored procedure no return value

A SQL Server stored procedure always returns a value. Even if you do not specify a return value, the stored procedure will return the default return value.

You cannot create a stored procedure that returns no value. Also, why would you want to do that? It is always a good practice to return something from the stored procedure. Return codes help to know the execution status of the stored procedure.

Read Alter Stored Procedure in SQL Server

SQL Server stored procedure return value 0

Till now, we have understood that whenever a procedure is executed, it returns an integer code. This integer code is called a return code, and it represents the execution status of any procedure.

Now, let’s understand what if a stored procedure returns 0 as a return code, and we will also implement an example related to it.

By default, if a stored procedure is executed successfully, the procedure will return 0 as the return code. And if the procedure returns any other non-zero integer, it means there is some failure in execution.

Let’s understand this concept by implementing an example. For this, consider the following stored procedure.

CREATE PROC usp_InsertRecords
( @name VARCHAR(50), @joining_date Date, @sales INT )
AS
BEGIN
  INSERT INTO dbo.SalesTable
  VALUES(@name, @joining_date, @sales)
END

In the above example, we have created a stored procedure that will accept 3 input parameters. And the procedure will use the 3 input parameters to insert a new record in the sales table.

Next, we have to execute the procedure, and we will use the following query for execution.

DECLARE @result INT
EXEC @result =  usp_InsertRecords 'Grady', '2021-02-23', 12000

SELECT @result AS 'Return Code'

In the above query, first, we have declared a variable of integer data type. And then, we are using the variable in the execution statement to store the value returned by the procedure.

Now, if the procedure is executed successfully, the procedure will return 0, and the value will be stored in the variable. And we can query the variable using the SELECT statement to get the output.

Read How to select latest record in SQL Server

SQL Server stored procedure return value -6

As discussed in the previous section, the return codes are used to indicate the execution status of a procedure. A 0 return code indicates the successful execution of the procedure in sql server, and any non-zero values indicate some error. Now, what if a stored procedure returns the negative value as a return code.

Generally, all negative value return codes indicate an error in the execution of the procedure. A -6 return code is used to indicate miscellaneous errors encountered due to the user. A simple example of this situation can be a division of a number by 0.

Let’s understand this concept by implementing the example of dividing a number by zero. For this, we have created the following stored procedure.

CREATE PROC usp_Division
AS
BEGIN
  SELECT 10/0 AS 'Division'
END

This stored procedure will simply try to divide the number 10 by zero (0). Next, let’s execute the procedure and store the return code in a variable.

DECLARE @result INT
EXEC @result = usp_Division

SELECT @result AS 'Return Code'

And now if we execute the procedure, the stored procedure will return the error message. And if we query the variable, it will return -6 as the return code.

This is an example of SQL Server stored procedure return value -6.

Read: Optional Parameters in SQL Server Stored Procedure

SQL Server stored procedure return value -4

In this section, we will try to understand the situation when a stored procedure returns -4 as a return code in sql server.

As -4 is a negative value, it means that the SQL Server might have encountered some error while executing the procedure. The -4 return code is used to represent some permission error while execution. A simple example could be when the user doesn’t have permission to use a particular table. But still, it is been used in a stored procedure.

Let’s demonstrate this scenario with help of an example. And for this, we have created the following stored procedure.

CREATE PROC usp_test
AS
BEGIN
  SELECT * FROM SampleTable
END

In the above example, we have created a simple stored procedure that uses the SELECT statement to fetch all the data from the table.

Note– Here we are using a user who doesn’t have permission to SELECT data from the sample table.

Next, to execute the stored and fetch the return code, we will use the following query.

DECLARE @result INT

EXEC @result = usp_test

SELECT @result AS 'Return Code'

After implementing the above example, the server will return a permission error. And if we try to query the variable, it will return -4 as a return code.

Read SQL Server stored procedure if else

SQL Server stored procedure return value output parameter

In SQL Server, we have the concept of output parameters. You can use the output parameters to return a result from a stored procedure. Output is declared just like the input parameters, but the OUTPUT keyword needs to be specified.

In this section, you will learn how you can use the output parameters. You will also see an example where we will use the output parameter to return a value.

The following stored procedure returns the sum of two numbers provided to it:

USE DemoDB
GO
CREATE PROCEDURE dbo.Addition @Num1 int, @Num2  int, @Sum int OUTPUT
AS
BEGIN
	SET @Sum= @Num1 + @Num2
END

• Now we will execute this stored procedure and store this value into a variable.

USE DemoDB
GO
DECLARE
	@Result int
EXEC dbo.Addition 78, 67, @Result OUTPUT
PRINT(@Result)

Thus, you might have learned how you can use the output parameters in SQL Server stored procedures to return values.

SQL Server stored procedure return identity value

In this section, you will learn how to return an identity value from a stored procedure in SQL Server. We will use the output parameters for returning the identity value.

Suppose you have a large table and you want to return the identity value of the last inserted record i.e. the highest value in the identity column from a stored procedure in SQL Server. We have created an example for a similar situation. We have created a stored procedure that will return the highest value of an identity column of a table.

• Consider the following table named NewEmployees.

• The following stored procedure will return the highest value of an identity column as the output parameter:

USE master
GO
CREATE PROCEDURE dbo.ReturnIdentity @Value int OUTPUT
AS
BEGIN
	SELECT @Value= MAX(EmpID) FROM dbo.NewEmployees
END

• When we will execute the stored procedure, we will pass a variable to the output parameter into which the identity value will be stored.

USE master
GO
DECLARE
	@Idvalue int
EXEC dbo.ReturnIdentity @Idvalue OUTPUT
PRINT(@Idvalue)

Thus, you might have learned how to return an identity value from a SQL Server stored procedure.

Read SQL Server stored procedure modified date

SQL Server stored procedure return resultset

A stored procedure in SQL Server can also return result sets directly back to the caller. For this implementation, we need to use the SELECT statement within the body of the stored procedure.

Let’s demonstrate this concept with help of an example. For this, we have created the following stored procedure.

CREATE PROC usp_GetAllRecords
AS
BEGIN
  SELECT [first_name], [last_name], [gender], [Country] FROM dbo.SampleTable
END
GO

In the above example, we have created a simple procedure usp_GetAllRecords. And we are using a SELECT statement within the body of the procedure to fetch four columns from a sample table.

Note- In this example, we have used a single SELECT statement to return one result set. But, we can also use multiple SELECT statements within a procedure to get multiple result sets.

Next, we just need to execute the procedure the result set will be directly sent to the caller.

EXEC usp_GetAllRecords
GO

After execution, the procedure will directly return the result set back to the caller or client application.

Now, from the output, we can observe that the execution of the stored procedure has returned a resultset. And this resultset consists of whole sample table data where the records are from different countries like the United States, Australia, New Zealand, etc.

SQL Server stored procedure return multiple resultsets

In SQL Server, you can return a result set from a stored procedure using a Select statement. You can also use multiple Select statements to return multiple result sets from a stored procedure. In this section, you will learn how you can return multiple resultsets from a SQL Server stored procedure.

Consider the following example of a stored procedure:

USE master
GO
CREATE PROCEDURE dbo.ReturnResultset
AS
BEGIN
	SELECT DISTINCT DepID FROM dbo.Employees
	SELECT DepID, DepName FROM dbo.Department
END

• We have used two Select statements inside the stored procedure. As a result, two resultsets will be returned when we execute the stored procedure.

USE master
GO
EXEC dbo.ReturnResultset

Thus, you might have understood how you can return multiple resultsets from a SQL Server stored procedure.

Read Loop in SQL Server stored procedure

SQL Server stored procedure return value varchar

A stored procedure in SQL Server generally uses a RETURN statement to return values to the caller. These values are called return codes, and these are used to represent the execution status of a procedure. But, these return codes are of integer data type. So, we can only use the RETURN statement to return integer values.

Now, to return a value of varchar data type, we have to use the OUTPUT parameter in the stored procedure. An output parameter is one of the types of parameters available in a stored procedure. And it is used to send data from the procedure back to the caller or client.

Let’s understand the implementation of an OUTPUT parameter in a stored procedure with the help of an example. In the example, we are going to create a procedure that accepts an integer value and returns the cube of it.

CREATE PROC usp_GetCube
(@a INT, @b INT OUTPUT)
AS
BEGIN
  SET @b = (@a * @a * @a)
END

• In the above example, we have created a stored procedure with 1 input parameter and 1 output parameter.
• To declare an OUTPUT parameter, we need to use the OUT or OUTPUT keyword while declaring a parameter.
• In our example, we have declared “@a” as the input parameter and “@b” as the output parameter. Both the parameters are of integer data type.
• After this, we are using the SET statement to calculate and assign the cube value to “@a” to “@b“.

Now, to get the result at the caller side, first, we need to declare a variable that can hold the value of the output parameter. And then, we need to pass the variable to the output parameter while execution. Here is the code for it.

DECLARE @x INT
EXEC usp_GetCube 21, @x OUTPUT

SELECT @x AS 'Result'

After execution, we need to query the variable to get the final result.

This is how to return varchar value from a SQL Server 2019 stored procedure.

Here is another example,

If you want to return values of varchar data type, you can use output parameters. Just declare an output parameter of varchar data type and store the value into it. To understand it better, we will create an example.

• Consider the following Student table.
• We have created a stored procedure that will take the student’s College ID as the input parameter and check if the record is present in the table.
• Further, it will return a message that will be passed through an output parameter of varchar data type.

USE master
GO
CREATE PROCEDURE dbo.ReturnString @ID int, @Result varchar(50) OUTPUT
AS
BEGIN
	IF EXISTS(SELECT * FROM dbo.Student WHERE [College ID]= @ID)
		SET @Result= 'Record Found In the Table'
	ELSE
		SET @Result= 'No Such Record'
END

• Now let us execute the stored procedure and provide a valid College ID as the input parameter.

USE master
GO
DECLARE
	@Result varchar(50)
EXEC dbo.ReturnString 1601, @Result OUTPUT
PRINT(@Result)

You can see that the stored procedure returned a varchar value and the desired result is printed on the output screen. Thus, you might have learned how you can return a varchar value from a SQL Server Stored Procedure.

Read How to view stored procedure in SQL Server

SQL Server stored procedure return value null

For returning a NULL value, you have to use the OUTPUT parameter. You cannot use the NULL value as the return code. If you try to return a NULL value using the Return statement, you will get a warning and the default return code will be returned. The following example shows how you can return a NULL value using an output parameter.

• We have created a stored procedure that will divide two numbers and gives us the result.
• The values are passed to the input parameters. We will verify that the second parameter must not contain a 0 value because the program will throw an error in that case.
• If the second parameter is 0, the stored procedure will return a NULL value. Otherwise, the stored procedure will return the result i.e. division of two numbers.

USE DemoDB
GO
CREATE PROCEDURE dbo.Division @Num1 real, @Num2 real, @Div real OUTPUT
AS
BEGIN
	IF @Num2= 0
		SET @Div= NULL
	ELSE
		SET @Div= @Num1 / @Num2
END

• The @Div is the output parameter that will give us the result.

Now let us execute the stored procedure.

• We need to declare a variable to stored the value returned by the stored procedure.
• To execute the procedure and store the result into the variable we need to write the query as:

USE DemoDB
GO
DECLARE @Result real
EXEC dbo.Division 34, 0, @Result OUTPUT
SELECT @Result AS Result

• We have passed 0 as the second number. As a result, the stored procedure will return a NULL value.

Thus, you might have learned how you can return a NULL value from a SQL Server stored procedure.

SQL Server stored procedure return more than one value

In SQL Server, you can also return multiple values from a stored procedure using output parameters. The following example will show you to do this.

• Consider the following example of a stored procedure that will return the data of a table named Employees.
• The stored procedure will take the Employee ID of an employee as the input parameter and fetch the details of the employee with the corresponding Employee ID.

USE master
GO
CREATE PROCEDURE dbo.ReturnMultiple @ID int, @Name varchar(20) OUTPUT, @Dep int OUTPUT
AS
BEGIN
	SELECT @Name= EmpName, @Dep= DepID
	FROM dbo.Employees WHERE EmpID= @ID
END

• To execute the stored procedure, write the SQL query as:

USE master
GO
DECLARE
	@EmpName varchar(20),
	@EmpDep int
EXEC dbo.ReturnMultiple 1007, @EmpName OUTPUT, @EmpDep OUTPUT
PRINT('Employee Name:' + @EmpName)
PRINT('Employee Department ID:' + STR(@EmpDep))

After reading this example, you might have understood how you can use the stored procedures to return multiple values in SQL Server.

SQL Server stored procedure return value datetime

If you want to return a value of DateTime data type, you can use the output parameters in a SQL Server stored procedure. You just have to declare an output parameter of DateTime type to which you will pass the DateTime value. We will create an example where we will return a DateTime value from a stored procedure using the output parameters.

• Consider the following OrderDetails table.

• The table stores the order details of a product selling company.
• We will create a stored procedure that will return the amount and date on which a particular order was place associated with a particular order ID.

USE master
GO
CREATE PROCEDURE dbo.FindDate @OrderNum int, @Date datetime OUTPUT, @Amount real OUTPUT
AS
BEGIN
	SELECT @Date= [Date], @Amount= [Amount] FROM dbo.OrderDetails
	WHERE [Order Number]= @OrderNum
END

• We need to declare the variables to store the values returned by the stored procedure.
• Now we will execute the stored procedure and store the values into variables.

USE master
GO
DECLARE
	@OrderDate datetime,
	@OrderAmount real
EXEC dbo.FindDate 8005, @OrderDate OUTPUT, @OrderAmount OUTPUT
PRINT('The order was placed on: '+ CONVERT(VARCHAR, @OrderDate))
PRINT('The amount of order is:' + CONVERT(VARCHAR, @OrderAmount))

• We have used the CONVERT function to convert the datetime data type value into VARCHAR type to concatenate with a string inside the PRINT statement.
• We have just printed the values. You can use these values anywhere in the calling procedure.

• You can see the date and time on which the order was placed.

Thus, you might have learned how you can return a value of DateTime data type from a stored procedure.

SQL Server stored procedure return boolean value

As discussed in the previous section, we can only use the RETURN statement to return integer values. So, we have to use the output parameter to implement this task. But, there is one more issue, there is no boolean data type in SQL Server. The alternative is to use bit data type instead of boolean.

A bit data type in SQL Server can either hold 1, 0, or NULL. In addition, we can easily create an output parameter of BIT data type. For demonstration, let’s create a stored procedure that returns bit values using the output parameter.

CREATE PROC usp_IsEven
( @a INT, @b BIT OUT )
AS
BEGIN
  IF(@a%2 = 0)
  BEGIN
    SET @b = 1
  END
  ELSE
  BEGIN
    SET @b = 0
  END
END

The above procedure is used to check whether the value passed as an input is even or not. If the input passed is even then, it will return 1, and if the value is odd, it will return 0. Next, we need to execute the procedure by providing the input value and a BIT data type variable.

DECLARE @x BIT
EXEC usp_IsEven 22, @x OUT 

SELECT @x AS 'IsEven'

In the end, we can use the BIT variable to get the result returned from the procedure.

This is how to return a boolean value from a SQL Server 2019 stored procedure.

Here is another example:

In SQL Server, there is a data type called bit that you can use to store boolean values. A bit variable can store one of the three values: 0, 1, NULL. You can define 1 as True and 0 as False.

You can return a bit value from the stored procedure using an output parameter. In the below example, you will see how we returned a bit value from a stored procedure.

• We have created a stored procedure that will check the existence of a record in a table named Student. If the particular record exists, a 1 i.e. True value will be returned. Else, 0 i.e. False will be returned.

USE master
GO
CREATE PROCEDURE dbo.ReturnBoolean @ID int, @Boolean bit OUTPUT
AS
BEGIN
	IF EXISTS(SELECT * FROM dbo.Student WHERE [College ID]= @ID)
		SET @Boolean= 1
	ELSE
		SET @Boolean= 0
END

• We need to store the returned value in a variable and pass that variable as an argument to the output parameter of the stored procedure.

USE master
GO
DECLARE
	@Result bit
EXEC dbo.ReturnBoolean 1601, @Result OUTPUT
IF @Result= 1
	PRINT('True')
ELSE
	PRINT('False')

Thus, you might have understood how you can return boolean values from a SQL Server stored procedure.

SQL Server stored procedure return value to a variable

In SQL Server, there are 2 methods to return a value from a stored procedure to a variable. The first method is by using the RETURN statement but we can only return integer values using it. The second method is by using output parameters in a stored procedure. Now, an output parameter can be of any valid data type.

Although, the examples related to both are also mentioned in previous sections in this tutorial. Let’s implement some different examples to make things clear.

Using RETURN statement

For this demonstration, we are creating a stored procedure that takes an input of integer data type. After this, it uses the input to calculate and return the square value of it.

CREATE PROC usp_GetSquare
( @a INT )
AS
BEGIN
  RETURN (@a*@a)
END

Now, to get the result returned from the procedure, we need to store the result into a variable at the time of execution. For this, first, we need to declare a variable of integer data type. And then, use the variable in the execution statement. The query for this implementation is given below.

DECLARE @x INT
EXEC @x = usp_GetSquare 13

SELECT @x AS 'Output'

After execution, we can use the SELECT statement to get the value from the variable. In the end, we will get the following output.

Using OUTPUT parameter

For this demonstration, we are creating a stored procedure that takes a string expression as input. After this, it calculates and returns the length of that expression using the OUTPUT parameter.

CREATE PROC usp_GetCharLength
( @string VARCHAR(MAX), @length INT OUT)
AS
BEGIN
  SET @length = LEN(@string)
END

Now, to get the value from the output parameter, we need to declare a variable of the same data type as a parameter. After this, we need to pass the variable as an output parameter while executing. The script for this implementation is given below.

DECLARE @x INT
EXEC usp_GetCharLength 'sqlserverguides', @x OUT

SELECT @x AS 'Result'

After execution, we can either use the SELECT or PRINT statement to display the result.

This is how to return value to a variable from a SQL Server 2019 stored procedure.

Read SQL Server stored procedure insert into with examples

SQL Server stored procedure return two values

In this section, we will learn how to return multiple values from a stored procedure in sql server 2019.

In SQL Server, we can easily return multiple values from a procedure to the caller by using the OUTPUT parameter. We can have multiple output parameters in a single stored procedure.

Let’s understand this implementation by executing an example on multiple output parameters. For demonstration, we are using the following product table.

Next, we are going to create a stored procedure that will return the product name and price based upon the product id passed as an input.

CREATE PROC usp_GetProductById
( @id INT, @name VARCHAR(MAX) OUT, @price INT OUT )
AS
BEGIN
  SELECT @name = [product_name], @price = [product_price] FROM dbo.ProductTable
  WHERE [product_id] = @id
END

In the example, the procedure has 1 input parameter and 2 output parameters. The input parameter is of integer data type, and it is used to pass product id from caller to procedure. And, the 2 output parameters are used to fetch product name and price.

Here is the code that we are using to execute the stored procedure.

DECLARE @p_name VARCHAR(MAX),
        @p_price INT

EXEC usp_GetProductById 101, @p_name OUT, @p_price OUT

SELECT @p_name AS [Product Name],
       @p_price AS [Product Price]

As there are 2 OUTPUT parameters in our stored procedure, so we need to have 2 variables to store the values from the parameters. In the end, after completing the execution, we will get the following result.

This is how to return two values from a stored procedure in sql server 2019.

Read SQL Server stored procedure vs function

SQL Server stored procedure return table

While working with a stored procedure, you might come across a situation where you need to process the result set returned from a procedure. Now, generally, we use a SELECT statement within a procedure to get the result set but, it is only useful to display the result.

A stored procedure in SQL Server cannot return a table or table-valued variable directly to the caller. Because the RETURN can only be used to return integer values, and the OUTPUT parameter doesn’t support table-valued variables. Additionally, we cannot use a stored procedure directly in the SELECT statement.

Now, to overcome the issue, we can use the following given below.

• First, use the SELECT statement within the procedure body to get the result set.
• After this, either create a standard table, temporary table, or table variable with the same column and data types as the result set.
• Next, store the result set returned from the procedure into the newly created table.
• In the end, we can query the table to get results.

Let’s understand this approach by implementing the example to store the result set into a table. For this, we have created the following stored procedure.

CREATE PROC usp_GetProduct
AS
BEGIN
  SELECT [product_id], [product_name], [product_price] FROM [ProductTable]
  WHERE [product_price] > 1000
END

In the above example, we have created a procedure that will return the result set from the product table where the product price is greater than 1000.

Next, to store the result set, we are creating a table variable with came columns as there in the result set. After this, we into to insert the result set into the table variable. In the end, we can easily query the table variable to get the result.

DECLARE @product_table AS TABLE                --declaring table variable
( p_id INT, p_name VARCHAR(MAX), p_price INT ) 

INSERT INTO @product_table                     --inserting into variable 
( p_id, p_name, p_price )
EXEC usp_GetProduct


SELECT * FROM @product_table                   --query the table variable

At the end of the execution, we will get the following result.

This is how to return a table from a sql server stored procedure.

Read SQL Server Row_Number

SQL Server stored procedure return value if exists

In SQL Server, many times we get into situations where the value we are referring to doesn’t exist in the table. And we get an empty result as an output.

Now, to solve this issue, we can create a stored procedure that checks the existence of the value and return the result according to that. For this implementation, we need to use the IF EXISTS clause within a stored procedure.

for this demonstration, let’s create a stored procedure that returns value only if it exists.

CREATE PROCEDURE CheckProductId
( @Id INT )
AS
BEGIN
DECLARE @name VARCHAR(max)
IF EXISTS(SELECT [product_id]
          FROM ProductTable
          WHERE [product_id] = @Id)
  BEGIN
    SELECT @name = [product_name] FROM [ProductTable]
    WHERE [product_id] = @id

    PRINT 'This product id exists in the table with product name ' + @name
  END
ELSE
  BEGIN
    PRINT 'This product id does not exists in the table'
  END
END

In the example, we have created a procedure that checks the existence of a product using the id of the product. If the procedure exists then, the procedure will return the product name, and if it doesn’t exist then, it will return a message.

Let’s check the result by passing product id 101 as an input to the procedure.

EXEC CheckProductId 101

And after execution, we will get the following output.

Note- We can also use the OUTPUT parameter to get the product name if it exists.

SQL Server stored procedure return value vs output parameter

In SQL Server, there are mainly 2 ways to return values from a stored procedure back to the caller. The first is by using the RETURN statement in the stored procedure, and the second is by using the OUTPUT parameter.

Now, there are many differences between both methods, and each of them is used for some specific task. So, in this section, we will try to understand the difference between both methods and discuss when to use which method.

• A RETURN statement in a stored procedure can only return an integer value. Whereas, an output parameter can be used to return values of any valid data type including an integer.
• A RETURN statement is used to give a return code back to the caller that indicates the execution status of a procedure. A 0 return code indicates successful execution, and a non-zero return code indicates some error in execution. It is mainly used to inform the success and failure of a stored procedure.
• A RETURN statement can only return one integer value at a time. But we can use multiple output parameters in a stored procedure to return multiple values together.

Now, here are some important points on when to use these methods to return value from a stored procedure.

• Generally, when we want to return multiple values, we should always use the output parameter.
• When we want to confirm the execution of a stored procedure, we should also use the return code.
• When we want to return a value of integer data type, it is better to use a RETURN statement.
• If we want to return values other than integer data type then, we should use the output parameter.

Here is the difference between SQL Server stored procedure return value vs output parameter.

SQL Server stored procedure return value vs output parameter

Return Value	Output Parameter
You can only return a single return code from a stored procedure.	You can return multiple values from a stored procedure.
The data type of a return value is always an integer. Therefore, you can return only integer values.	You can return values of various data types.
The return values are used to indicate the execution status of a stored procedure i.e. success or failure.	The values passed to through the output parameters can be used anywhere in the calling program.

SQL Server stored procedure return value –1

As discussed in the previous section, a non-zero return code indicates an error while executing a stored procedure. And same is the case when a stored procedure returns -1 as a return code.

The -1 return code indicates an error related to the use of the missing object in a procedure. It generally occurs when try to use another object such as tables, another procedure which do not exists in the database. Still, we are trying to use them in our procedure.

Now, the -1 return code generally occurs while working with applications where the object is referred to in the stored procedure. But, the object is not there in the database. It also occurs when we use the “SET NOCOUNT ON” statement in a procedure used to insert or update table records but due to some reason no row is updated.

SQL Server stored procedure returns negative value

If you are getting a negative return value from a stored procedure then your stored procedure has encountered some error. If the execution is successful, the stored procedure returns the default return code 0.

There are no fixed standards for negative values in SQL Server stored procedures. Try to debug the stored procedure if you have been getting a negative return value.

SQL Server stored procedure return value rows affected

To return the number of rows affected by any DML statement like Select, Insert, Update and Delete, you can use the @@ROWCOUNT system defined function. This function gives the number of rows affected after every such statement.

Its value gets updated after a new DML statement is executed. Therefore, if you want the number of rows affected by a DML statement, you should use this function immediately after the statement to avoid the loss of row count.

To return this row count from a SQL Server stored procedure, you need an output parameter. In this section, you will learn how you can return the row count from a stored procedure.

Consider the following stored procedure:

USE [master]
GO
CREATE PROCEDURE [dbo].[ListColumns] @TableName nchar(128), @Count int OUTPUT
AS
BEGIN
	SET NOCOUNT ON
	SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE
	FROM INFORMATION_SCHEMA.COLUMNS
	WHERE TABLE_NAME = @TableName
	SET @Count= @@ROWCOUNT
END

• This stored procedure returns the list of columns of a table. Also, it wll return the number of rows affected by the Select statement through an output parameter.
• We will store the value returned by the stored procedure in a variable.
• For example, we will use the SalesTable to fetch its columns’ list.
• This table has 5 columns. Therefore, the stored procedure should return 5 as the number of rows affected.

USE master
GO
DECLARE
@Rcount int
EXEC dbo.ListColumns 'SalesTable', @Rcount OUTPUT
PRINT('Number of Rows Affected: '+ CONVERT(VARCHAR, @Rcount))

• By default, you will see the resultset in the Results tab. Switch to the Messages tab to see the output.

Hence, in this way you can return the number of rows affected from a SQL Server stored procedure.

SQL Server stored procedure transaction return value

In this section, you will see an example where you will deal with a transaction in a SQL Server stored procedure and return a value based on the status of the transaction.

• The following stored procedure will insert record into the Persons table and the Phones table.

USE [master]
GO
CREATE PROCEDURE [dbo].[InsertIntoPersons] @FirstName nchar(10), @LastName nchar(10),
	@Age smallint, @Gender nchar(7), @Email nchar(30)='N/A', @Phone nchar(20)= 'N/A', @Location nchar(20)
AS
BEGIN
	BEGIN TRY
	BEGIN TRANSACTION
		INSERT INTO dbo.Phones( Phone, [First Name], [Last Name])
		VALUES( @Phone, @FirstName, @LastName)
		INSERT INTO dbo.Persons(
		[First Name], [Last Name], Age, Gender, Email, Phone, Location)
		VALUES( @FirstName, @LastName, @Age, @Gender, @Email, @Phone, @Location)
		COMMIT TRANSACTION
		RETURN 2
	END TRY
	BEGIN CATCH
		ROLLBACK TRANSACTION
		RETURN 3
	END CATCH
END

• If both the Insert statements successfully works, the transaction will be commmited. Otherwise if the second statement causes an error, the previous insert statement will be rolled back.
• We have defined the return code 2 in case of commited transaction and the return code 3 in case the transaction is rolled back.
• Let us try to execute the stored procedure:

USE master
GO
DECLARE
@Code int
EXEC @Code= dbo.InsertIntoPersons 'Veruca', 'Williams', 45, 'Female',@Phone='+1 354 542 643', @Location= 'Los Angeles'
PRINT(@Code)

• The rows are inserted and 2 as a return value is returned showing that the transaction is commited.

• The First Name column of the Persons table does not accept NULL value. Thus, the transaction will be rolled back if we try to give a NULL value to the First Name column.

USE master
GO
DECLARE
@Code int
EXEC @Code= dbo.InsertIntoPersons NULL, 'Williams', 45, 'Female',@Phone='+1 354 542 643', @Location= 'Los Angeles'
PRINT(@Code)

• You can see that a return value 3 is returned because the transaction is rolled back.

Thus, you might have understood how you return values when dealing with transactions in SQL Server stored procedures.

SQL Server stored procedure return value best practices

In this section, we will discuss some best practices associated with the stored procedure return values.

The return values or return codes tell the execution status of stored procedures. A return code of a stored procedure tells whether the stored procedure was executed successfully or not.

You can define your own return codes based on some conditions in a stored procedure. However, there are some default return codes also that are returned if you do not specify your own return values like 0 for successful execution. Let us discuss some things that you should follow while returning data from a stored procedure.

• The return codes are of integer type. Do not try to return values other than the integer data type.
• Use return codes only for getting the execution status. If you want to return some other data, use output patrameters instead, even if the data is of integer type.
• You can also use return codes for getting the type of error encountered inside the stored procedure. But, you should not use it. Instead, you should use error handling mechanisms( Try- Catch block).
• There are some default return codes. Try not to use them explicitly. This can result in ambiguity.

These were some best practices that you should follow while returning values from SQL Server stored procedures.

You may like the following sql server tutorials:

• SQL Server Add Column + Examples
• IDENTITY_INSERT in SQL Server
• SQL Server drop table if exists
• Exception Handling in SQL Server
• MySQL vs SQL Server – Key Differences in Details
• Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements

So, in this tutorial, we have discussed How to return values from a stored procedure. We have also discussed why a stored procedure returns some specific values, and we have covered the following topics.

• SQL Server stored procedure return value
• SQL Server stored procedure return value data type
• SQL Server stored procedure default return value
• SQL Server stored procedure no return value
• SQL Server stored procedure return value 0
• SQL Server stored procedure return value -6
• SQL Server stored procedure return value -4
• SQL Server stored procedure return value output parameter
• SQL Server stored procedure return identity value
• SQL Server stored procedure return resultset
• SQL Server stored procedure return multiple resultsets
• SQL Server stored procedure return value varchar
• SQL Server stored procedure return value null
• SQL Server stored procedure return more than one value
• SQL Server stored procedure return value datetime
• SQL Server stored procedure return boolean value
• SQL Server stored procedure return value to a variable
• SQL Server stored procedure return two values
• SQL Server stored procedure return table
• SQL Server stored procedure return value if exists
• SQL Server stored procedure return value vs output parameter
• SQL Server stored procedure return value –1
• SQL Server stored procedure returns negative value
• SQL Server stored procedure return value rows affected
• SQL Server stored procedure transaction return value
• SQL Server stored procedure return value best practices