SQL Server: execute Temp table inside dynamic query

Question

I am working with temp table and dynamic query as well as. my requirement is in temp table I am inserting some unique value. After that from temp table I get unique key and implement inside where condition.

Here is code

Temp table:

Create Table #TempUniqueKeyTable
(
     ID BIGINT IDENTITY PRIMARY KEY, 
     ukey uniqueidentifier
)

INSERT INTO #TempUniqueKeyTable (ukey) 
    SELECT followedTo 
    FROM tblFollowers 
    WHERE FollowedBy = @uniqueKey

INSERT INTO #TempUniqueKeyTable (ukey) 
    SELECT connectionRequestTo 
    FROM tblConnection 
    WHERE connectRequestBY = @uniqueKey

INSERT INTO #TempUniqueKeyTable (ukey) 
    SELECT connectRequestBY 
    FROM tblConnection 
    WHERE connectionRequestTo = @uniqueKey

Dynamic query:

SET @QueryHeader = 'SELECT * from XYZ B'
SET @MainQuery = @QueryHeader
SET @MainQuery += ' WHERE B.uniquekey in (SELECT distinct ukey FROM #TempUniqueKeyTable  WHERE ukey='''+CONVERT(NVARCHAR(MAX),@UniqueKey)+''')) '

EXEC (@MainQuery)

It's not working in condition. This part not returning any value

SELECT distinct ukey 
FROM #TempUniqueKeyTable  
WHERE ukey = ''' + CONVERT(NVARCHAR(MAX), @UniqueKey) + ''')

When I run PRINT command it show generated query like

SELECT * 
FROM XYZ B 
WHERE B.uniquekey IN (SELECT DISTINCT ukey 
                      FROM #TempUniqueKeyTable  
                      WHERE ukey = '1EA8658C-F978-4DC7-9ABF-E4FF253A2284')

It should be like

SELECT * from XYZ B 
  WHERE B.uniquekey in ('A5B6BC01-5FEB-4554-90A7-4B5ADCC00D36','F75C6303-40EB-4119-A45A-D122E8118FA4')

You have to declare / create it inside your dynamic statement. Just build your string into a single statement. — S3S, Mar 23 '17 at 21:50
@scsimon so what will code, i havn't idea what to write. i tried lots of thing — ANJYR, Mar 23 '17 at 21:52
No worries. I'm on my mobile but someone will have an answer in a minute I'm certain. — S3S, Mar 23 '17 at 21:53
I think that the temp table can be accessed from within the dynamic query ([rextester demo](http://rextester.com/GEKQS44354)). So I guess the problem is with the syntax (an extra parenthesis) - can't be sure though without some sample data (including XYZ). — Giorgos Altanis, Mar 23 '17 at 22:06
After your edit: what is happening when you copy-paste the **printed** command to a query window and execute it? — Giorgos Altanis, Mar 23 '17 at 22:10
nothing happen because after in command it required uniquekey. check last line of question. — ANJYR, Mar 23 '17 at 22:16
I see, no, you cannot do that with your approach; SQL does exactly what is supposed to do, the printed statement is correct. Do you know anything about cursors? — Giorgos Altanis, Mar 23 '17 at 22:22
You must build the `IN` clause piece-by-piece, iterating over the data in the temp table using a cursor (or alternatively a `while` loop). Do you know how to do this? — Giorgos Altanis, Mar 23 '17 at 22:24
i haven't work that type. if you have share with me else i will search — ANJYR, Mar 23 '17 at 22:29
I updated my answer, I hope it will help you solve your problem. It is a more-or-less standard technique. On the other hand, make sure you understand why your original approach did not produce a comma-separated list of values! — Giorgos Altanis, Mar 23 '17 at 22:38

Giorgos Altanis · Accepted Answer · 2017-03-23T22:36:48.630

You have an extra right parenthesis:

SET @MainQuery+= ' WHERE B.uniquekey in (SELECT distinct ukey FROM #TempUniqueKeyTable WHERE ukey='''+CONVERT(NVARCHAR(MAX),@UniqueKey)+''') '

But I guess this is something you could have easily identified yourself. Perhaps I didn't understand the question?

EDIT You can use this approach to build your IN clause. Extra care should be taken to ensure that @MainQuery returns the correct results (i.e, nothing) when the temp table has no data.

declare @QueryHeader NVarchar(max);
declare @MainQuery NVarchar(max);


SET @QueryHeader='SELECT * from XYZ B'

if exists (select * from #TempUniqueKeyTable)
begin
    SET @MainQuery = @QueryHeader;
    declare @WhereClause nvarchar(max);
    set @WhereClause = ' WHERE B.uniquekey in (';

    declare @curr uniqueidentifier;
    select @curr = min(ukey) from #TempUniqueKeyTable;
    while @curr is not null
    begin
        set @WhereClause = @WhereClause + '''' + cast(@curr as nvarchar(40)) + ''',';
        select @curr = min(ukey) from #TempUniqueKeyTable where ukey > @curr;
    end

    set @WhereClause = left(@WhereClause, len(@WhereClause) - 1) + ')';

    set @MainQuery = @QueryHeader + @WhereClause;
end
else
-- make sure nothing is returned, for example:
begin
    set @MainQuery = @QueryHeader + ' WHERE 1 = 0'; 
end

EXEC (@MainQuery);

The `while` loop builds the `IN` clause step-by-step, until there are no more records in the temp table. — Giorgos Altanis, Mar 23 '17 at 22:42

SQL Server: execute Temp table inside dynamic query

1 Answers1