Greetings, I have a problem as follows:
I have an SQL variable declared:
DECLARE @myVariable nvarchar(max)
SET @myVariable = 'Select ROWGUID from MySampleTable'
SELECT ROWGUID FROM myTable WHERE ROWGUID in (exec sp_executesql @myVariable )
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
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:
If you are using SQL Server 2005 or 2008, you could use a Common Table Expression instead of creating a temporary table.
Your CTE should look like:
WITH Records (ROWGUID) AS ( SELECT ROWGUID FROM MySimpleTable )
Then you can simply use:
SELECT ROWGUID FROM myTable WHERE ROWGUID IN (SELECT ROWGUID FROM Records);
Which potentially means you can drop your Variable.
Ok, so CTE is out of the question. But, what about using a Table variable instead of creating a Temporary Table.
DECLARE @myVar nvarchar(max); DECLARE @table TABLE ( ROWGUID uniqueidentifier ) SET @myVar = 'SELECT ROWGUID FROM MySampleTable'; INSERT INTO @table EXEC sp_executesql @myVar; SELECT ClientID FROM myTable WHERE EXISTS (SELECT ClientID FROM @table);