SQL Server Stored Procedure Input Parameter, Output Parameter and Return Value

By: Rick Dobson   |   Updated: 2022-02-24   |   Comments (2)   |   Related: > Stored Procedures

Problem

As a beginning SQL Server Developer \ DBA, I recently became comfortable placing T-SQL scripts into a stored procedure template to make the code from a script easily re-usable. However, I want to grow my proficiency with stored procedures for specifying and using input parameters and output parameters. I also seek a code example which will grow my understanding of how to specify and use return code values.

Solution

This tip is part of a series on using different kinds of SQL Server code modules. The series focuses on returning either rowsets or scalar values from code modules, including stored procedures, user-defined functions, and views. An earlier tip, Create, Alter, Drop and Execute SQL Server Stored Procedures, in this series, focused on how to create, alter, and run stored procedures. This tip complements the earlier one by drilling down on how to make the operation of stored procedures dynamic with input parameters, output parameters and return codes.

• An input parameter can determine which subset of rows a stored procedure will return from a select statement within it.
• A value for an output parameter can be returned to a calling script. The output parameter value may be based on an aggregate function or any computational expression within the stored procedure.
• Return codes from a return statement within a stored procedure can achieve two goals. First, each return statement within a stored procedure can force an exit from a stored procedure whenever it is encountered. Second, integer values associated with return statements can indicate the location from which a stored procedure exits to its calling T-SQL script. Also, see the kick-off tip in this series for another example of using return code values.

Examples Specifying and Using Input Parameters for SQL Server Stored Procedures

The following script demonstrates one approach to specifying a pair of input parameters for a stored procedure named uspMyThirdStoredProcedure in the dbo schema. See a prior tip, Create, Alter, Drop and Execute SQL Server Stored Procedures, in this series to see an example of a stored procedure with a single input parameter.

• The code block starts by setting a default database of CodeModuleTypes. You can use any other database you prefer for the database context.
• Before invoking the create proc statement, the code drops a prior version of the stored procedure if one exists. The drop proc statement references the stored procedure by the schema name (dbo) in which it resides as a qualifier for the stored procedure object name (uspMyThirdStoredProcedure).
• After the stored procedure name in the create proc statement, two input parameters are specified. Each input parameter specification consists of a parameter name followed by a data type. Parameter names must begin with an @ symbol.
  • The @SalesPersonID parameter has an int data type. This is an integer value to denote a salesperson.
  • The @Sales_Yr parameter also has an int data type. This is a four-digit integer value to designate the year during which an order is placed.
• After the as keyword, a select statement designates a results set based on a pair of nested queries.
  • An inner query joins SalesOrderHeader, SalesPerson, and Person tables. Fields are returned from both SalesOrderHeader and Person tables. The left join between the SalesOrderHeader table and the SalesPerson table makes it possible to flag (as a null value) any SalesPersonID field values in the SalesOrderHeader table that are not in the SalesPerson table as a BusinessEntityID value. The left join between the Person table and the SalesPerson table also flags first and last name field values as null if the SalesPerson BusinessEntityID field value does not match any BusinessEntityID field value from the Person table.
    • SalesPersonID is an integer value identifier for the salesperson.
    • FirstName and LastName fields are from the Person table and contain the first and last names of the salesperson.
    • SaleOrderID is an integer identifier for an order.
    • Sales_Yr is a computed field based on the year for an order.
    • TotalDue is the total sales amount associated with an order.
  • The outer query has three main functions.
    • First, it groups orders by SalesPersonID, FirstName, LastName, and Sales_Yr.
    • Second, it computes two aggregated fields for each group:
      • the count of orders grouped by year for each salesperson
      • the sum of the sales amounts grouped by year for each salesperson
    • Third, it extracts a particular row from the set of orders grouped by salesperson and Sales_Yr. The having clause specifies SalesPersonID must equal @SalesPersonID and Sale_yr must equal @Sales_Yr.

use CodeModuleTypes
go
 
-- conditionally drop a stored proc
if object_id('dbo.uspMyThirdStoredProcedure') is not null 
     drop proc dbo.uspMyThirdStoredProcedure
go
 
-- create dbo.uspMyThirdStoredProcedure stored proc so that it accepts two input parameters
create proc dbo.uspMyThirdStoredProcedure
   @SalesPersonID int
  ,@Sales_Yr int
as
 
-- count and sum of total orders 
-- by SalesPersonID, FirstName, LastName, and Sale_Yr
select 
   SalesPersonID
  ,FirstName
  ,LastName
  ,Sale_Yr
  ,count(SalesOrderID) Total_Orders
  ,sum(TotalDue) Total_Sales
from
(
  -- list of SalesOrderIDs with SalesPersonID
  select
     SalesPerson.BusinessEntityID SalesPersonID
    ,[Person].FirstName
    ,[Person].LastName
    ,[SalesOrderHeader].[SalesOrderID]
    ,[SalesOrderHeader].[OrderDate]
    ,[SalesOrderHeader].[TotalDue]
    ,YEAR([SalesOrderHeader].[OrderDate]) Sale_Yr
  from [AdventureWorks2014].[Sales].[SalesOrderHeader]
  left join [AdventureWorks2014].[Sales].[SalesPerson]
    on SalesOrderHeader.SalesPersonID = SalesPerson.BusinessEntityID
  left join [AdventureWorks2014].[Person].[Person]
    on Person.BusinessEntityID = SalesPerson.BusinessEntityID
  where OnlineOrderFlag != 1 -- exclude online sale orders that have no salesperson
) for_total_sales_by_salesperson
group by
   SalesPersonID
  ,FirstName
  ,LastName
  ,Sale_Yr
having 
  SalesPersonID = @SalesPersonID
  and Sale_Yr = @Sales_Yr

You can designate values for input parameters within a stored procedure at the time that you run it. You can run a stored procedure with an exec statement that designates the name of the stored procedure you want to run. After the stored procedure name in the exec statement, you can assign values to the input parameters based on either parameter position after the stored procedure name within the create proc statement , parameter name, or both. However, there is one restriction as indicated in the script below.

Each of the first three exec statements end with the go keyword. This keyword causes each exec statement to operate in a separate batch so that an error in any one batch does not cause other subsequent statements to be bypassed. The fourth exec statement has no code following it. All four exec statements attempt to assign the same two values to the @SalesPersonID and @Sales_Yr parameters.

• The first exec statement shows how to assign values by position to both parameters. First, 274 is assigned as the @SalesPersonID value. Next, 2014 is assigned as the @Sales_Yr value.
• The second exec statement shows how to assign values by name for both parameters.
• The third exec statement illustrates a mixed assignment rule with position used for @SalesPersonID and name used for @Sales_Yr.
• The fourth exec statement fails because of a syntax error. In this case, the @SalesPersonID parameter is first assigned its value by name followed by an attempt to assign a value to @Sales_Yr by position – that is, without using a name. The error occurs because after you assign a value to any input parameter by name then all remaining input parameters must also have their value specified by name.

-- assign @SalesPersonID and @Sales_Yr input parameters based on position
exec dbo.uspMyThirdStoredProcedure 274, 2014
go
 
-- assign @SalesPersonID and @Sales_Yr input parameters
-- with parameter name, assignment operator (=), and its value
exec dbo.uspMyThirdStoredProcedure @SalesPersonID = 274, @Sales_Yr = 2014
go
 
-- assign input parameters with first parameter un-named
-- and second parameter named
-- these assignments succeed
exec dbo.uspMyThirdStoredProcedure 274, @Sales_Yr = 2014
go
 
-- assign input parameters with first parameter named
-- and second parameter un-named
-- these assignments fail
exec dbo.uspMyThirdStoredProcedure @SalesPersonID = 274, 2014

Here are the results sets from the first three exec statements. Panes appear in order for the first, second, and third exec statements. Each pane has the same SalesPersonID value and the same Sale_Yr value. No matter whether the input parameter values are set by position, parameter name, or a combination of position first and name second, the query outcome from the stored procedure is the same.

As mentioned, the fourth exec statement failed. The following screen shot shows the error message from the Messages tab. You can see that error number is 119. The associated error message indicates that the second and subsequent input parameters (if there are more than two) must be passed by first designating the name and then the value(s) for the second and subsequent input parameters. This technique corresponds to the third exec statement. However, you could also use either of the other two preceding exec statement formats for designating parameter values at run time.

Examples Specifying and Using Output Parameters for SQL Server Stored Procedures

This section shifts the main focus away from input parameters and towards output parameters although the sample for this section uses input parameters as well as output parameters.

The next script creates a new version of uspMyThirdStoredProcedure in the dbo schema. The script in this section starts by conditionally dropping the prior version of the stored procedure if there is already one in the dbo schema of the default database context, such as one designated by the last use statement.

• The new version of the stored procedure returns one set of output parameter values with the total number of orders and total sales amount for a salesperson identified by last name. The preceding example generated the same kind of output but from a rowset with a single row instead of a set of three scalar values from output parameters.
• The create proc statement includes five parameter specifications. The parameter declarations appear after the stored procedure name and before the as keyword.
  • The three output parameter names are @LastName, @Total_Orders, and @Total_Sales_Amount.
    • Each output parameter must be followed by either of two keywords: output or out.
    • The stored proc assigns a value to each output parameter in its outer query.
  • @SalesPersonID and @Sales_Yr are input parameters. Both parameters serve as criteria values for counting sales orders and accumulating sales amounts for a particular salesperson during a specific year.
• After the as keyword, there is T-SQL code for populating the output parameters with the help of a pair of nested queries that rely on input parameter values to help populate output parameters.
  • The inner query generates a list of sales orders by salesperson with other relevant data, such as SalesOrderID to identify individual orders and TotalDue to denote the final amount for individual sales orders.
  • The inner query also lists sales orders with order date to identify the year during which a sale was made as well as SalesPersonID to denote the salesperson making a sale.
  • The outer query contains a count function to total the number of SalesOrderID values and a sum function for the TotalDue field to aggregate sales amounts across sales orders.
  • A having clause in the outer query additionally restricts the output so that accumulated sales values (SalesOrderID and TotalDue) are for the @Sales_Yr year and the @SalesPersonID salesperson.

-- conditionally drop a stored proc
if object_id('dbo.uspMyThirdStoredProcedure') is not null 
     drop proc dbo.uspMyThirdStoredProcedure
go
 
create proc dbo.uspMyThirdStoredProcedure
   @SalesPersonID int
  ,@Sales_Yr int
  ,@LastName nvarchar(50) output
  ,@Total_Orders int output
  ,@Total_Sales_Amount money output
as
 
-- count and sum of total orders by SalesPersonID with LastName
select 
   @LastName = LastName
  ,@Total_Orders = count(SalesOrderID) 
  ,@Total_Sales_Amount = sum(TotalDue) 
from
(
  -- list of SalesOrderIDs with SalesPersonID
  select
     SalesPerson.BusinessEntityID SalesPersonID
    ,[Person].FirstName
    ,[Person].LastName
    ,[SalesOrderHeader].[SalesOrderID]
    ,[SalesOrderHeader].[OrderDate]
    ,[SalesOrderHeader].[TotalDue]
    ,YEAR([SalesOrderHeader].[OrderDate]) Sale_Yr
  from [AdventureWorks2014].[Sales].[SalesOrderHeader]
  left join [AdventureWorks2014].[Sales].[SalesPerson] 
    on SalesOrderHeader.SalesPersonID = SalesPerson.BusinessEntityID
  left join [AdventureWorks2014].[Person].[Person]
    on Person.BusinessEntityID = SalesPerson.BusinessEntityID
  where OnlineOrderFlag != 1 -- exclude online sale orders that have no salesperson
) for_total_sales_by_salesperson
group by
   SalesPersonID
  ,FirstName
  ,LastName
  ,Sale_Yr
having 
  SalesPersonID = @SalesPersonID
  and Sale_Yr = @Sales_Yr
go

The next script shows the T-SQL for displaying the output parameter values along with the @Sale_Yr input parameter. The output parameter values are assigned in uspMyThirdStoredProcedure. However, SQL Server requires you to assign the output parameter to local variables before you can reference them outside of the stored procedure. The example below uses the same names for output parameters and their corresponding local variables.

While output parameters are returned from a stored procedure, input parameters are not returned without special code development. If you need an input parameter in the code block calling a stored procedure, you can use a local variable for the input parameter with the following steps.

• Declare a local variable for the input parameter that you want to reference after running the stored procedure. Assign a value to the local variable for the input parameter either in the declare statement or in a separate set statement. Also, use the same declare statement to designate local variables to receive output parameter values in the exec statement for running a stored procedure.
• Invoke an exec statement with local variables for the output parameter values as well as any input parameters that you will need to reference locally.
  • Embedded assignment statements in the exec statement can capture output parameters in local variables.
  • Use the local variables for one or more input parameters just like any constants that you would use to assign values to input parameters.
• After the stored procedure concludes and returns the output parameters to local variables in the exec statement, you can use the local variables for the input parameter just like the local variable with the output parameter values.

Here is a review of the code block below which demonstrates these guidelines.

• The declare statement specifies four local variables.
  • Three of these (@LastName, @Total_Orders, @Total_Sales_Amount) are for storing output parameter values from a stored procedure.
  • The fourth local variable (@Sales_Yr) is for storing an input parameter value. A subsequent set statement assigns a value of 2014 to the @Sales_Yr local variable.
• Next, the exec statement is invoked with a local variable for one of the two input parameters and a local variable for each output parameters.
  • The @SalesPersonID name denotes the first input parameter. This parameter helps to constrain stored procedure output so that the BusinessEntityID column in the SalesPerson table must equal the @SalesPersonID parameter value. The code below passes a value of 274 to @SalesPersonID, which points at a salesperson named Stephen Jiang.
  • The @Sales_Yr name denotes the second input parameter. The parameter helps to constrain the output from the stored procedure based on the Sale_Yr column values from the inner query’s results set. The input parameter is assigned the value of the @Sales_Yr local variable (2014).
  • The @LastName local variable is assigned the @LastName output parameter value in the third assignment within the exec statement. The value for this parameter is Jiang.
  • The @Total_Orders local variable is assigned the @Total_Orders output parameter in the fourth assignment within the exec statement. The value for this parameter is 8.
  • The @Total_Sales_Amount local variable is assigned the @Total_Sales_Amount output parameter in the fifth assignment within the exec statement. The value for this parameter in the example is 201288.5196.
• The select statement at the end of the script displays the three output parameter values passed to local variables as well as the local variable value passed to an input parameter.

-- declare local variables for output parameters
declare
 @lastName nvarchar(50)
,@Sales_Yr int 
,@Total_Orders int
,@Total_Sales_Amount money
 
-- set local variable for subsequent assignment to input parameter in exec statement
-- and for local use in select statement after exec statement
set @Sales_Yr = 2014
 
-- invoke stored proc and assign output parameters to local variables
exec dbo.uspMyThirdStoredProcedure 
 @SalesPersonID = 274
,@Sales_Yr = @Sales_Yr
,@LastName = @LastName output
,@Total_Orders = @Total_Orders output
,@Total_Sales_Amount = @Total_Sales_Amount output
 
-- display local variables with output parameters
select 
 @LastName LastName
,@Sales_Yr Sales_Year
,@Total_Orders Total_Orders
,@Total_Sales_Amount Total_Sales_Amount

The result set below shows the output from the final select statement in the preceding script. There is a single row of output with values for three output parameters and one input parameter. This screen shot reveals how to display both output and input parameters in a single select statement after an exec statement runs a stored procedure.

Examples Specifying and Using Return Codes for SQL Server Stored Procedures

The following script generates within this tip the third fresh copy of uspMyThirdStoredProcedure in the dbo schema. The stored procedure has if statements that control access to one of three begin...end blocks. A return statement within each begin…end block exits the stored proc with a return code value of 1, 2, or 3. Within each block, you could also add code to execute a select statement, insert a row of values into a table, update a set of values in a table, or whatever else you want to perform with T-SQL. The return code value from the stored procedure indicates the path pursued within the stored procedure.

Within uspMyThirdStoredProcedure, access to one of the three begin…end blocks depends on the @SalesPersonID input parameter value.

• An @SalesPersonID value of less than 274 results in a return code value of 1.
• An @SalesPersonID value of greater than 290 results in a return code value of 2.
• @SalesPersonID values of greater than or equal 274 and less than or equal to 290 result in a return code value of 3.

-- conditionally drop a stored proc
if object_id('dbo.uspMyThirdStoredProcedure') is not null 
     drop proc dbo.uspMyThirdStoredProcedure
go

-- simple control flow example with return codes 
create proc dbo.uspMyThirdStoredProcedure 
@SalesPersonID int 
as 
 
   -- control flow code 
   if @SalesPersonID < 274 
   begin 
      -- place here code to execute when @SalesPersonID < 274 
      return (1) 
   end; 
  
   if @SalesPersonID > 290 
   begin 
      -- place here code to execute when @SalesPersonID > 290 
      return (2) 
   end; 
  
   begin 
      -- place here code to execute when 
      -- @SalesPersonID >= 274 and 
      -- @SalesPersonID <= 290 
      return (3) 
end; 

You can retrieve a return code value with two steps. First, you need to declare a local variable, such as @return_status in the following script, to which to transfer the return code value from a stored procedure. Second, you need to assign the return code value from within the stored procedure’s exec statement to the local variable.

The script below illustrates the syntax for accomplishing these steps for three different values of @SalesPersonID; each submitted input parameter value results in a different path being taken within the stored procedure. An embedded assignment statement inside the exec statement for uspMyThirdStoredProcedure transfers the return code value from the stored procedure to a local variable (@return_status). Finally, a pair of select statements echoes the values of the input parameter (@SalesPersonID) and the return code value (@return_status).

-- declare local variable for return code value
declare @return_status int, @SalesPersonID int;
 
-- @SalesPersonID = 273 results in a Return Status of 1
set @SalesPersonID = 273
exec @return_status = dbo.uspMyThirdStoredProcedure @SalesPersonID;
select @SalesPersonID input_parameter
select 'Return Status' = @return_status
 
 
-- @SalesPersonID = 291 results in a Return Status of 2
set @SalesPersonID = 291
exec @return_status = dbo.uspMyThirdStoredProcedure @SalesPersonID;
select @SalesPersonID input_parameter
select 'Return Status' = @return_status
 
-- @SalesPersonID = 274 results in a Return Status of 3
set @SalesPersonID = 274
exec @return_status = dbo.uspMyThirdStoredProcedure @SalesPersonID;
select @SalesPersonID input_parameter
select 'Return Status' = @return_status

Here’s what the output from the preceding script looks like.

• When the input parameter value is 273, then the return code value is 1.
• When the input parameter value is 291, then the return code value is 2.
• When the input parameter value is 274, then the return code value is 3.

Next Steps

• You can try the code examples for this tip on a computer with the AdventureWorks database. Instructions for downloading the database are available from this prior MSSQLTips.com tip.
• Next, copy the script(s) that you want to test and modify. Confirm your copied code generates valid results for the AdventureWorks database.
• Finally, modify the script to work in another database of your choice to start creating and running stored procedures with your scripts in your databases.
• See these related items:
  • SQL Server RETURN and OUTPUT Clause in Stored Procedures
  • SQL Server Stored Procedure Tutorial

About the author

Rick Dobson is an author and an individual trader. He is also a SQL Server professional with decades of T-SQL experience that includes authoring books, running a national seminar practice, working for businesses on finance and healthcare development projects, and serving as a regular contributor to MSSQLTips.com. He has been growing his Python skills for more than the past half decade -- especially for data visualization and ETL tasks with JSON and CSV files. His most recent professional passions include financial time series data and analyses, AI models, and statistics. He believes the proper application of these skills can help traders and investors to make more profitable decisions.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips