The system stored procedure sp_executesql can be used to run dynamic sql. It also provides a way to use input and/or output parameters. However, the BOL examples seemed to be mighty complicated so I put this together for a quick reference.
For the example, we have a simple table "Cities" like this:
City nvarchar(50),
ST nvarchar(2),
County nvarchar(50)
If all you want to do is to look up the County for Phoenix AZ you could just write
SELECT County
FROM Cities
WHERE City = 'Phoenix'
AND ST = 'AZ'
To make this more versatile, you could write a stored procedure like this
CREATE PROC Get_County
(
@City nvarchar(50),
@ST nvarchar(2)
)
AS
SELECT County
FROM Cities
WHERE City = @City
AND ST = @ST
GO
However, pulling data from from a stored procedure might require that you create a temp table and then use the INSERT...EXEC syntax, which does work, but nesting of sp's is limited.
If we turn this into dynamic sql, we can feed in different parameters. It does seem a bit complicated but it is flexible once you get it set up...
DECLARE
@County nvarchar(50),
@County_out nvarchar(50),
@City nvarchar(50),
@ST nvarchar(2),
@sql nvarchar(4000)
SET @sql = 'SELECT @County_out = County FROM Cities WHERE City = @City AND ST = @ST'
EXEC sp_executesql
@sql,
N'@City nvarchar(50), @ST nvarchar(2), @County_out nvarchar(50) OUTPUT',
@City = 'Phoenix',
@ST = 'AZ',
@County_out = @County OUTPUT
SELECT @County AS County
So the steps are:
+ declare all of your input and output variables
+ declare a variable that's used locally for the lookup value
+ 1st argument = the sql string
+ 2nd argument = comma-separated list to declare of all of the in/out parameters
+ next argument(s) = input parameter(s)
+ last argument = output parameter
