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.