Creating output parameters
To create an output parameter for a stored procedure, you use the following syntax:
| 1 | parameter_name data_type OUTPUT |
A stored procedure can have many output parameters. In addition, the output parameters can be in any valid data type e.g., integer, date, and varying character.
For example, the following stored procedure finds products by model year and returns the number of products via the @product_count output parameter:
| 123456789101112131415 | CREATE PROCEDURE uspFindProductByModel ( @model_year SMALLINT, @product_count INT OUTPUT) ASBEGIN SELECT product_name, list_price FROM production.products WHERE model_year = @model_year; SELECT @product_count = @@ROWCOUNT;END; |
In this stored procedure:
First, we created an output parameter named @product_count to store the number of products found:
| 1 | @product_count INT OUTPUT |
Second, after the SELECT statement, we assigned the number of rows returned by the query(@@ROWCOUNT) to the @product_count parameter.
Once you execute the CREATE PROCEDURE statement above, the uspFindProductByModel stored procedure is compiled and saved in the database catalog.
If everything is fine, SQL Server issues the following output:
| 1 | Commands completed successfully. |
Calling stored procedures with output parameters
To call a stored procedure with output parameters, you follow these steps:
- First, declare variables to hold the value returned by the output parameters
- Second, use these variables in the stored procedure call.
For example, the following statement executes the uspFindProductByModel stored procedure:
| 1234567 | DECLARE @count INT; EXEC uspFindProductByModel @model_year = 2018, @product_count = @count OUTPUT; SELECT @count AS ‘Number of products found’; |
The following picture shows the output:
