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