Execute query stored in variable in a very specific way

Question

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

UPDATE Please read what I've written where is the problem:

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:

Answer 1

If you are using SQL Server 2005 or 2008, you could use a Common Table Expression instead of creating a temporary table.

Common Table Expressions

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.

---

EDIT

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);

Answer 2

Maybe you could use User Defined Functions instead of stored procedures?