What We Have Now.
SQL Server 2000 DTS introduced the ability for us to parameterise some of our statements (DataPump Task, Data Driven Query Task and the
ExecuteSQL task). We do this by specifiying a placeholder in our statement like this.
SELECT
<column list>
FROM
<table>
WHERE
<column> = ?
We then can map Global Variables to the placeholders and at runtime the values in the global variables will replace those values from the design time in
the statement. This is all very well but there are a few drawbacks.
- Only the SQL Server Connection has any chance of using it.
- The design time does not like complex statements using it.
What SQL Server 2005 Brings.
The upcoming release of SQL Server 2005 brings with it greater flexibility in using parameters namely.
- More connection managers understand them.
- The ability to map variables to input, output and return values.
The difficulty with this is that each data provider is different and wants things done in their own way. We spent
a good while trying to figure out what the values required for the parameter properties were. Kirk Haselden has done the work
for us though and has produced this fantastic grid to show us what needs to go where and we reproduce it here with his
kind permission.
| Connection Manager type |
Parameter Marker |
Parameter Name |
Example Query |
Example Parameter Name |
| OLEDB |
? |
0,1,2,... |
SELECT Name FROM People WHERE Name = ? |
0 |
| ADO.Net |
@Varname |
@Varname |
SELECT Name FROM People WHERE Name = @Name |
@Name |
| ADO |
? |
@Param1, @Param2,... |
SELECT Name FROM People WHERE Name = ? |
@Param1 |
| ODBC |
? |
1,2,... |
SELECT Name FROM People WHERE Name = ? |
1 |
Hopefully this table will save you pulling out your hair trying to figure the values for yourself. For
an example article using this table look no further than here (The ExecuteSQL Task)
Thanks a million Kirk