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

Answer 1

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;