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.<\/p>\n\n\n\n
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”.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
It is also possible and useful to retrieve it in a variable to use it in the following T-SQL code. <\/p>\n\n\n\n
First of all, create the clients table, the example code for the creation of the table is available here :<\/p>\n\n\n\n
-- Create the clients table to store the number, the name and the city\nCREATE TABLE [dbo].[CLIENTS] (\n [ClientNumber] \tint IDENTITY(1,1),\n [Name] \tnvarchar(20) UNIQUE,\n [City] \t\tnvarchar(20)\n)\nGO\n<\/pre>\n\n\n\nExample 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.<\/p>\n\n\n\n-- Creatie a stored procedure with the variable @CityCustomer as OUTPUT\nCREATE PROCEDURE dbo.uspClientCity\n\t@ClientName nvarchar(20),\n\t@CustomerCity nvarchar(20) OUTPUT\nAS\n\tSELECT \t@ClientCity= CITY\n\tFROM \tdbo.CLIENTS\n\tWHERE \t[Name] = @ClientName\nGO\n<\/pre>\n\n\n\nHere 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.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
Execute the SQL Server stored procedure with OUTPUT in 3 steps<\/h3>\n\n\n\n
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.<\/p>\n\n\n\n-- Declare the variable to store the city\nDECLARE @ClientCity nvarchar(20)\n\n-- Execute the stored procedure\nEXEC dbo.uspClientCity\n\t@ClientName = 'SERGEI',\n\t@CustomerCity = @CustomerCity OUTPUT\n\n-- Select the client city from the variable\nSELECT @ClientCity as [City];\n<\/pre>\n\n\n\nOUTPUT returns the result of a T-SQL command in a variable<\/h2>\n\n\n\n
Finally, the result of the stored procedure is the value of the city for the selected client.<\/p>\n\n\n\n
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<\/a>.<\/p>\n\n\n\n
For more scripting examples, here is how to modify a SQL Server stored procedure<\/a><\/strong> with ALTER PROCEDURE.<\/p>\n\n\n\n