How to execute Dynamically created query in sql server

Question

I have written a stored procedure Where I have written a query to get userid. There is a separate database for every userid. So I am trying to run a select query based on this userid obtained from my previous select query in a loop.

And I am trying to assign the columns in this select query to variables declared and use them further. But I am not understanding how to assign these to variables as I am getting errors

    USE DATABASE1
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO

    CREATE PROCEDURE [User].[update_client_details] 
    AS
    DECLARE
    @clientdata CURSOR,
    @clientid INT,
    @SQL VARCHAR(2000),
    @uid INT
    @isactive INT,
    @createdDate Date

    BEGIN
        SET @clientdata = CURSOR FOR
        SELECT clientuserid FROM User.queen_client


        OPEN @clientdata
        FETCH NEXT
        FROM @clientdata INTO @clientid
        WHILE @@FETCH_STATUS = 0
        BEGIN
            SET @SQL = N'SELECT @uid=userid, @isactive=isactive, @createdDate=createddate FROM ['+CAST(@clientid AS NVARCHAR(20))+'].User.queen_user';
            EXEC (@SQL)
            IF(@isactive = 1)
            BEGIN
            //do someting//
            END
        END

        CLOSE @clientdata
        DEALLOCATE @clientdata

    END

if the execute the store procedure it is getting executed and not stopping. If I force stop the execution then I am getting the error as "must declare the scalar variable "uid""

Query Which I tried

EXEC sys.sp_executesql N'SELECT @uid=userid, @isactive=isactive, @createdDate=createddate FROM ' +QUOTENAME(@clientid)+'.QueenBase.queen_user', N'@clienid int, @uid int OUTPUT, @createDate date OUTPUT';

Thom A · Accepted Answer · 2020-06-09T12:16:25.727

2

Variables only persist and exist within the scope that they are declared in. Therefore both the following batches will fail:

DECLARE @I int = 1;
EXEC (N'SELECT @i;');

GO

EXEC (N'DECLARE @I int = 1;');
SELECT @i;

When using dynamic SQL, don't use EXEC(@SQL);, use sp_executesql. Then you can parametrise the statement. For example:

DECLARE @I int = 1;
EXEC sys.sp_executesql N'SELECT @i;', N'@i int', @i;

This returns 1. If you need to return a value to the outer SQL, as a parameter, you need to use OUTPUT parameters:

DECLARE @I int = 10;
DECLARE @O int;

EXEC sys.sp_executesql N'SELECT @O = @I / 2;', N'@I int, @O int OUTPUT', @I, @O OUTPUT;

SELECT @O;

This assigns the value 5 to the variable @O (which is then selected).

Also, don't use N'...[' + @SomeVariable + N'] ...' to inject dynamic values, it's not injection safe. Use QUOTENAME: N'...' + QUOTENAME(@SomeVariable) + N'...'

Additional note. The fact that you need to do something like N'FROM ['+CAST(@clientid AS NVARCHAR(20))+'].User.queen_user' suggests a severe design flaw, but that's a different topic.

If you do fancy additional reading, I cover a lot of considerations you need to take into account in my article Dos and Don'ts of Dynamic SQL.

For your attempt, it's not working as you use an expression for the first parameter (not a literal or variable) and then don't pass any of the parameters you define:

DECLARE @SQL nvarchar(MAX) = N'SELECT @uid=userid, @isactive=isactive, @createdDate=createddate FROM ' +QUOTENAME(@clientid)+'.QueenBase.queen_user;';

EXEC sys.sp_executesql @SQL, N'@isactive int OUTPUT, @uid int OUTPUT, @createDate date OUTPUT', @isactive OUTPUT, @uid OUTPUT, @createDate OUTPUT;

edited Jun 09 '20 at 12:16

answered Jun 09 '20 at 11:42

Thom A

88,727
11
45
75

Can you please help me in rewriting the query in the format which you are explained SELECT @uid=userid, @isactive=isactive, @createdDate=createddate FROM ['+CAST(@clientid AS NVARCHAR(20))+'].User.queen_user – Sai sri Jun 09 '20 at 11:53
What failed when you tried, @Saisri , or what part did you not understand and I can try to elaborate more, – Thom A Jun 09 '20 at 11:57
I have tried the query which I posted in the question above – Sai sri Jun 09 '20 at 12:09
Yes, and I explained in the above @Saisri , don't use `EXEC (@SQL)` as you can't parametrise it. Use `sys.sp_executesql`, like I demonstrate above. – Thom A Jun 09 '20 at 12:09
I have edited the question and I have added the query tried under the heading "Query Which I tried" – Sai sri Jun 09 '20 at 12:10
The first parameter needs to be a literal or variable, not an expression, @Saisri . YOu then need to actually pass the parameters to that SP. – Thom A Jun 09 '20 at 12:11
Can you please correct my query and type it down. So that I can understand better – Sai sri Jun 09 '20 at 12:13
Added an **untested** version, @Saisri . – Thom A Jun 09 '20 at 12:16
a small help. How do I loop parameter SQL query output? – Sai sri Jun 09 '20 at 13:32
If you're wanting to loop you have another problem, @Saisri . Looping in SQL Server always been you have a major design flaw. Use Set-based solutions. – Thom A Jun 09 '20 at 13:33
I am new to sql. I think its beyond my knowledge to understand what your saying. Can you help me out in this? – Sai sri Jun 09 '20 at 13:45
You're asking a new question there, @Saisri , so you should be posting a new question. But, like I said, you should not be looping in SQL, it's a set-based language not a programmatical one. – Thom A Jun 09 '20 at 13:47
So what do I need to do because the output of SQL parameter query here is not a single row. and I need to verify each row output using an IF statement – Sai sri Jun 09 '20 at 13:49
... *"You're asking a new question there, @Saisri , so you should be [posting a new question](https://stackoverflow.com/questions/ask)."* – Thom A Jun 09 '20 at 13:50
Ok I have created a new question https://stackoverflow.com/questions/62284453/how-to-loop-a-query-that-is-created-dynamically-in-sql-server – Sai sri Jun 09 '20 at 14:01
Are u looking into the new question? – Sai sri Jun 09 '20 at 14:54

How to execute Dynamically created query in sql server

Query Which I tried

1 Answers1