See How to return a value from dynamicaly executed T-SQL code FAQ #8130

>I have a stored proc that uses the following code:
>

>set @var = (select col from table where pk = @param)
>

>
>What I would like to do is make 'table' a variable to make the sp more generic. Something like this would be nice (but this won't work).
>

>set @sql = 'select col from ' + @table + ' where pk = ' + @param
>set @var = (exec sp_executesql @sql)
>

>
>Any ideas how to set a variable to the result of a dynamically constructed sql statement?
>
>TIA
>
>John