Execute query stored in variable in a very specific way
- by niao
Greetings, I have a problem as follows:
I have an SQL variable declared:
DECLARE @myVariable nvarchar(max)
a third party library set a value for this variable. To simplify, lets say that the value is as follows:
SET @myVariable = 'Select ROWGUID from MySampleTable'
Now, I want to execute the following query:
SELECT ROWGUID FROM myTable WHERE ROWGUID in (exec sp_executesql @myVariable )
However, the above statement does not work because it returns an error telling me that I can't execute stored procedure in that way. I made a workaround and this is what I wrote:
create table #temptable (ID uniqueidentifier null)
if(@myVariable is not null AND @myVariable !='') insert into #temptable exec sp_executesql @myVariable
SELECT ROWGUID FROM myTable WHERE ROWGUID in (select * from #temptable)
DROP TABLE #temptable
This works fine.However I don't think it is a good idea to use temporary table. How can I achieve the same result without necessity of creating temporary tables?
I am using SQL SERVER 2005
