As a SQL Server Developer or DBA, it is important to have a strong understanding of how to work with input parameters, output parameters, and return codes in stored procedures. These concepts allow you to make your stored procedures more dynamic and flexible. In this article, we will explore how to specify and use input parameters, output parameters, and return codes in SQL Server stored procedures.

Specifying and Using Input Parameters

Input parameters allow you to pass values into a stored procedure. These values can be used to filter data or perform calculations within the stored procedure. To specify an input parameter, you need to declare it in the stored procedure’s parameter list. The parameter name must begin with an “@” symbol, followed by the parameter name and data type.

Here is an example of how to specify input parameters in a stored procedure:

CREATE PROCEDURE dbo.uspMyThirdStoredProcedure
   @SalesPersonID int,
   @Sales_Yr int
AS
BEGIN
   -- Your code here
END

Once you have specified the input parameters in the stored procedure, you can assign values to them when you run the stored procedure. You can assign values by position or by name. Here are examples of both approaches:

-- Assign values by position
EXEC dbo.uspMyThirdStoredProcedure 274, 2014

-- Assign values by name
EXEC dbo.uspMyThirdStoredProcedure @SalesPersonID = 274, @Sales_Yr = 2014

Specifying and Using Output Parameters

Output parameters allow a stored procedure to return values back to the calling script. These values can be based on calculations or aggregations performed within the stored procedure. To specify an output parameter, you need to declare it in the stored procedure’s parameter list and use the “OUTPUT” keyword.

Here is an example of how to specify output parameters in a stored procedure:

CREATE PROCEDURE dbo.uspMyThirdStoredProcedure
   @SalesPersonID int,
   @Sales_Yr int,
   @LastName nvarchar(50) OUTPUT,
   @Total_Orders int OUTPUT,
   @Total_Sales_Amount money OUTPUT
AS
BEGIN
   -- Your code here
END

To retrieve the values of the output parameters, you need to declare local variables and assign the output parameter values to them. Here is an example:

DECLARE @LastName nvarchar(50), @Total_Orders int, @Total_Sales_Amount money;

EXEC dbo.uspMyThirdStoredProcedure @SalesPersonID = 274, @Sales_Yr = 2014, @LastName = @LastName OUTPUT, @Total_Orders = @Total_Orders OUTPUT, @Total_Sales_Amount = @Total_Sales_Amount OUTPUT;

SELECT @LastName AS LastName, @Total_Orders AS Total_Orders, @Total_Sales_Amount AS Total_Sales_Amount;

Specifying and Using Return Codes

Return codes allow a stored procedure to indicate the success or failure of its execution. Return codes can be used to control the flow of the script or to provide information about the execution status. To specify a return code, you need to use the “RETURN” statement within the stored procedure.

Here is an example of how to specify return codes in a stored procedure:

CREATE PROCEDURE dbo.uspMyThirdStoredProcedure
   @SalesPersonID int
AS
BEGIN
   -- Your code here
   IF @SalesPersonID < 274
   BEGIN
      -- Code to execute when @SalesPersonID < 274
      RETURN 1
   END;

   IF @SalesPersonID > 290
   BEGIN
      -- Code to execute when @SalesPersonID > 290
      RETURN 2
   END;

   -- Code to execute when @SalesPersonID is between 274 and 290
   RETURN 3
END

To retrieve the return code value, you need to declare a local variable and assign the return code value from the stored procedure’s execution. Here is an example:

DECLARE @return_status int, @SalesPersonID int;

SET @SalesPersonID = 273;
EXEC @return_status = dbo.uspMyThirdStoredProcedure @SalesPersonID;
SELECT @SalesPersonID AS input_parameter, 'Return Status' = @return_status;

By understanding how to specify and use input parameters, output parameters, and return codes in SQL Server stored procedures, you can make your code more dynamic and flexible. These concepts allow you to create reusable code and control the flow of your scripts based on different conditions. Experiment with these concepts in your own stored procedures to enhance your SQL Server development skills.