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:
If all you want to do is to look up the County for Phoenix AZ you could just write
WHERE City = 'Phoenix'
AND ST = 'AZ'
To make this more versatile, you could write a stored procedure like this
CREATE PROC Get_County
WHERE City = @City
AND ST = @ST
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...
SET @sql = 'SELECT @County_out = County FROM Cities WHERE City = @City AND ST = @ST'
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