Return values with SQL Server stored procedure

How to create a SQL Server stored procedure to return values with OUTPUT? This simple T-SQL example returns values through output parameters. Setting up, instantiating, and using the output parameters of a stored procedure is similar to that of the input parameters.

The major difference is the OUTPUT clause after the parameter name. The OUTPUT keyword specifies that the stored procedure must return a value. On the other hand, the output clause can be specified using either the keyword “OUTPUT” or simply “OUT”.

Return values into variables with a SQL Server stored procedure using the output keyword

How to retrieve and store the result of a stored procedure after an execute command in a SQL Server variable? Indeed, during the call of the commands execute SQL or EXEC, it is easy to display the result with the PRINT function.

It is also possible and useful to retrieve it in a variable to use it in the following T-SQL code.

First of all, create the clients table, the example code for the creation of the table is available here :

-- Create the clients table to store the number, the name and the city
CREATE TABLE [dbo].[CLIENTS] (
   [ClientNumber] 	int IDENTITY(1,1),
   [Name]       	nvarchar(20) UNIQUE,
   [City]     		nvarchar(20)
)
GO

Example of a T-SQL procedure to return data with the OUTPUT option
In fact, this example procedure returns the name of the city of a customer whose name is passed as an input parameter.

-- Creatie a stored procedure with the variable @CityCustomer as OUTPUT
CREATE PROCEDURE dbo.uspClientCity
	@ClientName nvarchar(20),
	@CustomerCity nvarchar(20) OUTPUT
AS
	SELECT 	@ClientCity= CITY
	FROM 	dbo.CLIENTS
	WHERE 	[Name] = @ClientName
GO

Here is an example of a script to create a SQL Server stored procedure with OUTPUT to retrieve and reuse the result in one or more variables after the execution of an Exec command.

Indeed, in T-SQL with the EXEC command, followed by the name of the procedure, use the keyword “OUTPUT”. This allows to store the result from the called procedure and to send it to the caller.

Execute the SQL Server stored procedure with OUTPUT in 3 steps

First, declare a variable of the same type as the OUTPUT return and the City column, i.e. nvarchar(20) for our example.
Second, execute the procedure with the EXEC command.
Select the variable @CityCustomer that contains the result of the stored procedure.

-- Declare the variable to store the city
DECLARE @ClientCity nvarchar(20)

-- Execute the stored procedure
EXEC dbo.uspClientCity
	@ClientName = 'SERGEI',
	@CustomerCity = @CustomerCity OUTPUT

-- Select the client city from the variable
SELECT @ClientCity as [City];