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];
OUTPUT returns the result of a T-SQL command in a variable
Finally, the result of the stored procedure is the value of the city for the selected client.
To go further on stored procedures, here is how to insert rows in a stored procedure. This example passes the values as parameters in variables.
For more scripting examples, here is how to modify a SQL Server stored procedure with ALTER PROCEDURE.