Global Variables and Stored Procedure Parameters

By Darren Green
Version 2000
Level Intermediate

Input Parameters

One of the benefits of the SQL Server 2000 is that the SQL tasks support mapping of global variables to parameter placeholders within the SQL. This means you can use the global variable as an input parameter for your stored procedure.

Create a new package, adding a SQL Server connection pointing to the database where you have created the sample stored procedure. Next add an Execute SQL Task, with the code to call your stored procedure, using a question mark as the placeholder for your parameter:

EXEC dbo.spInputTest ?

Once you have entered the SQL statement you can click Parameters and you will be presented with the Input Parameters tab. The mapping list view will have one row for each placeholder found in the SQL. There parameters have no meaningful names, they are listed in order, so Parameter 1 will be the first meaningful found, Parameter 2 will be the second placeholder found on so on. For each parameter found you can select a global variable to map to that parameter. At run time the provider (ODBC or OLE-DB) parameter support will be invoked and the mapped global variable value will be substituted for the placeholder.

It is important to note that the parameter handling uses the provider parameter support since not all providers support parameters, and others provide only limited or unreliable support. Obviously the Microsoft SQL Server providers have full support.

Another important point about parameters is that just like when using local variables in SQL they cannot be used to parameterise object names such as procedure names, table names or column names. In a normal SQL statement you can workaround this by using dynamic SQL, but in DTS you have the option of dynamically building the SQL statement. For more on this strategy see Global Variables and SQL statements in DTS

Ouput Parameters

You can also capture stored procedure output parameters, although this cannot be done through the same placeholder mechanism. This simple example demonstrates how you can capture stored procedure return values and output parameters into global variables using the Output Parameter functionality of the Execute SQL Task in SQL Server 2000.

The Execute SQL Task supports two types or output parameter types, row values and rowsets. There is no placeholder functionality as used for input parameters. Confusion often arises when people try and retrieve values from the input parameter placeholder. In this case we have to use a SELECT statement to return our values as normal query rowset and map them in this way.

To build a simple demonstration package, first create the stored procedure below:

CREATE PROCEDURE dbo.spReturnAndOutputTest 
    @OutTest int OUTPUT
AS
  SET @OutTest = 9
  RETURN 1

Create a new package, adding a SQL Server connection pointing to the database where you have created the sample stored procedure. Next add an Execute SQL Task, with the code to call our stored procedure and return the values as a query result or rowset:

DECLARE @ReturnValue int, @OutTest int
EXEC @ReturnValue=dbo.spReturnAndOutputTest @OutTest OUTPUT
SELECT @ReturnValue AS ReturnValue, @OutTest AS OutTest

Once you have entered the SQL statement you can click Parameters and select the Output Parameters tab. Select the Output Parameter Type of Row Value. The columns of our SELECT, the values we want to return, are shown in the left hand column of the Parameter Mapping grid, using the names specified as column aliases in the SELECT statement. They can now be mapped to global variables of your choice.

Input and Output Parameters

To use both input and ouput parameters for the same stored procedure call, a combination of the two methods described above might look like this:

DECLARE @OutputTest int
EXEC dbo.spInputAndOutputTest ?,  @OutputTest OUTPUT
SELECT @OutputTest AS OutputTest

Unfortunately when you click on the Parameters button you will get the following error:

Error Source: Microsoft OLE DB Provider for SQL Server
Error Description: Syntax error or access violation

The error is raised by the design-time syntax validation routine. Fortunately the run-time routine is more forgiving than the design-time routine. The way to workaround this is to substitute your stored procedure call with a simple SQL statement that produces the same meta-data, which will allow you to map the parameters as required. Below is our sample stored procedure:

CREATE PROCEDURE dbo.spInputAndOutputTest 
    @InputTest int,
    @OutputTest int OUTPUT
AS
  SET @OutputTest = @InputTest * 2

The following simple SQL statement, whilst functionaly different, produces the same meta-data as our real SQL statement:

SELECT id AS OutputTest FROM sysobjects where id = ?

Enter the meta-data matching SQL and click Parameters. We can then map the input parameter as in the first example. Next select Output Parameters and go onto select a Row Value output parameter as in the second example. Once complete close Execute SQL Task.

The parameter mapping has now been done, but to complete the operation you need replace the matching SQL with the real SQL. To do this open the Disconnected Edit feature (found on the Package menu). Expand the Tasks node, and select the correct Execute SQL Task, e.g. DTSTask_DTSExecuteSQLTask_1. Now double click the SQLStatement property for our selected task, and overwrite the matching SQL with the real SQL as shown at the top of this example. Execute the package.

In summary, you can use some dummy SQL to setup the parameter mapping at design-time, but then change the SQL afterwards. This does mean that if you ever need to change the parameter mappings you must repeat the substitution process to setup the new mappings.

Using SET NOCOUNT ON

If your stored procedure performs one or more DML operations that cause informational resultsets to be generated, then these will confuse the task. An informational resultset will be familiar to anyone that uses Query Analyser, as you will have seen them as messages in results, for example (1 row(s) affected). These messages are indistinguishable from the SELECT resultset you are really interested in, and since the task reads the first resultset it encounters, an informational resultset can mask your real data. To suppress this behaviour add the SET NOCOUNT ON statement to the top or your stored procedure or script.