Tip #26 – Input Variables in SQL Stored Procedures

Advantage Stored Procedures use virtual tables for both input and output they are __input and __output. There are several approaches to obtaining the input values for use within your procedure. My personal favorite is to create a cursor for the __input table. With this method I can access each of the values in the table using dot notation, see the example below:

   1: -- Get total sales for Customer and Sales rep

   2: DECLARE cInput CURSOR AS SELECT * FROM __input;

   3:  

   4: OPEN cInput;

   5: FETCH cInput;

   6:  

   7: INSERT INTO __output SELECT COUNT(InvoiceNo), SUM(InvoiceTotal) FROM Orders

   8: WHERE CustomerID = cInput.CustId AND EmployeeID = cInput.EmpId;

   9:  

  10: CLOSE cInput;

The __input table will have one and only one row so only a single FETCH is required. You can also use variables to store the input values which would not require using a cursor, see the example below:

   1: -- Get total sales for Customer and Sales rep

   2: DECLARE @iCustId INTEGER;

   3: DECLARE @iEmpId INTEGER;

   4:  

   5: SET @iCustId = SELECT CustId FROM __input;

   6: SET @iEmpId = SELECT EmpId FROM __input;

   7:  

   8: INSERT INTO __output SELECT COUNT(InvoiceNo), SUM(InvoiceTotal) FROM Orders

   9: WHERE CustomerID = @iCustId AND EmployeeID = @iEmpId;

In this example the amount of code is about the same, however, more input variables would require more variables in the second method and the first method requires no additional code.

As a final note you can combine the two methods if you need to manipulate one of your input variables.

   1: DECLARE cInput CURSOR as SELECT * FROM __input;

   2: DECLARE @dAmount as MONEY;

   3:  

   4: OPEN cInput;

   5: FETCH cInput;

   6:  

   7: @dAmount = cInput.BudgetAmount;

   8:  

   9: ...