Retrieving Values from SQL Exec()

Never had a good reason to do this up until now, but if you need to get a value from a generated sql string, there are a few hoops you must jump through. Example:

declare @table 		varchar(20) 
,@username varchar(30)

--can't use double quotes w/out this :)
SET QUOTED_IDENTIFIER OFF

--set your variables
select @table = 'tblcustomers'
,@username = 'webmaster'

--create a temp table to hold your value
create table #temp (field varchar(50))

--insert the value into temp table
insert into #temp
exec ('select email from ' + @table + ' where username = "' + @username + '"')

--voila
select field from #temp

drop table #temp

Reference: Getting Creative with EXEC and EXEC()

Leave a Reply

Your email address will not be published. Required fields are marked *