SQL Server stored procedure return value

In this SQL Server tutorial, we will discuss how to return value from stored procedure and how to return some specific values from a stored procedure, etc.

SQL Server stored procedure return value

In SQL Server 2019, there are mainly 3 methods for returning some values from a SQL stored procedure to its caller. All three 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 represent the procedure’s execution status.
• 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 directly return result sets from a procedure to the caller by 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 parameters.
• However, an output parameter will not return its value directly to the caller. First, we have to declare a variable that can hold the value of the output parameter.
• During 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. To use 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.

In this article, we will discuss the implementation of all these methods further and 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 data types, 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

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

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

By default, if a stored procedure is executed successfully, it returns 0 as the return code. If the procedure returns any other non-zero integer, it means there was 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 use them to insert a new record in the sales table.

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

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

SELECT @result AS 'Return Code'

In the above query, we first declare an integer-type variable. Then, we use the variable in the execution statement to store the value returned by the procedure.

If the procedure is executed successfully, it will return 0, and the value will be stored in the variable. 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 method in sql server, and any non-zero values indicate some error. What if a stored procedure returns the negative value as a return code.

Generally, all negative value return codes indicate an error in the procedure’s execution. A -6 return code indicates miscellaneous errors encountered by the user. A simple example of this situation is dividing 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 try to divide the number 10 by zero (0). Next, execute the method and store the return code in a variable.

DECLARE @result INT
EXEC @result = usp_Division

SELECT @result AS 'Return Code'

Now, if we execute the procedure, the stored procedure will return an error message. 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 an error while executing the procedure. The -4 return code represents a permission error during execution. A simple example could be when the user doesn’t have permission to use a particular table. But it is still used in a stored procedure.

Let’s demonstrate this scenario with an example. We have created the following stored procedure for this.

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—We are using a user who doesn’t have permission to SELECT data from the sample table.

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

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 to use the output parameters. You will also see an example of using 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 to 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 to do so.

Suppose you have a large table and 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 a table’s identity column.

• 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 to the caller. For this implementation, we need to use the SELECT statement within the stored procedure’s body.

Let’s demonstrate this concept with an example. 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. We use a SELECT statement within the procedure’s body to fetch four columns from a sample table.

Note—In this example, we have used a single SELECT statement to return one result set. However, 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 stored procedure execution has returned a resultset. This resultset consists of whole sample table data with records from different countries, such as 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. This section will teach you how to 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 in 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 the 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. 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 will create a procedure that accepts an integer value and returns its cube.

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 parameters are integer data types.
• After this, we use the SET statement to calculate and assign the cube value to “@a” and “@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 for 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 the varchar data type, you can use output parameters. Just declare an output parameter of the varchar data type and store the value in it. To understand it better, we will create an example.

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

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

To return 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 to 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, as the program will throw an error if it does.
• If the second parameter is 0, the stored procedure will return a NULL value. Otherwise, it will return the result, which is the 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 store 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, so 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 an employee’s Employee ID 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 to use 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 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 returns the amount and date on which a particular order was placed 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 to return a value of the 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 a bit data type instead of boolean.

A bit data type in SQL Server can hold 1, 0, or NULL. In addition, we can quickly create an output parameter for the 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 checks whether the value passed as an input is even or odd. If the input passed is even, it will return 1, and if the value is odd, it will return 0. Next, we must 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'

Ultimately, 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 example below, you will see how we returned a bit of 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. Otherwise, a 0, i.e., False value 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 stored procedure’s output parameter.

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

The examples related to both are also mentioned in previous sections of 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 integer data type input. The procedure then uses the input to calculate and return its square value.

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 it in a variable at the time of execution. First, we need to declare a variable of integer data type. Then, we 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

We are creating a stored procedure for this demonstration 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

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 must 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, the OUTPUT parameter allows us to easily return multiple values from a procedure to the caller. 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 will create a stored procedure that returns the product name and price based on 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

The procedure has 1 input parameter and 2 output parameters in the example. The input parameter is of integer data type, and it is used to pass the product ID from the caller to the procedure. The 2 output parameters are used to fetch the 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 two 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 encounter a situation where you need to process the result set returned from a procedure. Generally, we use a SELECT statement within a procedure to get the result set, but it is only useful for displaying the result.

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

To overcome the issue, we can use the information below.

• First, use the SELECT statement within the procedure body to get the result set.
• After this, create a standard, temporary, 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 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 to return the result set from the product table where the product price exceeds 1000.

Next, to store the result set, we create a table variable with the same columns as the result set. After this, we insert the result set into the table variable. Ultimately, 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.

Read SQL Server Row_Number

SQL Server stored procedure return value if exists

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

To solve this issue, we can create a stored procedure that checks the existence of the value and returns the result accordingly. We need to use the IF EXISTS clause within a stored procedure for this implementation.

For this demonstration, let’s create a stored procedure that returns a 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 its ID. If the procedure exists, it will return the product name, and if it doesn’t, it will return a message.

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

EXEC CheckProductId 101

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 two main ways to return values from a stored procedure to the caller. The first is by using the RETURN statement in the stored procedure, and the second is by using the OUTPUT parameter.

There are many differences between both methods, and each is used for a specific task. In this section, we will try to understand the differences between both methods and discuss when to use which method.

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

Here are some important points about when to use these methods to return a value from a stored procedure.

• Generally, we should always use the output parameter when we want to return multiple values.
• When we want to confirm the execution of a stored procedure, we should also use the return code.
• It is better to use a RETURN statement when we want to return a value of the integer data type.
• If we want to return values other than integer data type, 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 using the missing object in a procedure. It generally occurs when trying to use another object, such as tables or another method, that does not exist in the database. We are still 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 is not 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 for 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 succeeds, the stored procedure returns the default code 0.

There are no fixed standards for negative values in SQL Server stored procedures. If you have been getting a negative return value, try debugging the stored procedure.

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 to return the row count using 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 and, through an output parameter, the number of rows affected by the Select statement.
• 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.

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 transaction’s status.

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

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 Insert statements successfully work, the transaction will be committed. Otherwise, if the second statement causes an error, the previous insert statement will be rolled back.
• We have defined return code 2 for a committed transaction and return code 3 for a rolled-back transaction.
• 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 values. Thus, if we try to give the First Name column a NULL value, the transaction will be rolled back.

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 SQL Server stored procedures transactions.

SQL Server stored procedure return value best practices

This section 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 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 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 to get the execution status. If you want to return other data, use output parameters instead, even if the data is of integer type.
• You can also use return codes to determine the type of error encountered inside the stored procedure. However, you should not use them. 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.

You should follow These best practices 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 above topics.