Set Variable with select query with dynamic schema name

Question

I am trying to set a variable as a query result. My problem is that below code runs in a while loop & schemaName is always different.

WHILE (@i <= (SELECT MAX(idx) FROM @schema_table))

BEGIN
SET @userid = (SELECT AspNetUsers.Id
                FROM schemaName.AspNetUsers
                LEFT JOIN schemaNameAspNetUserRoles ON  AspNetUserRoles.UserId = AspNetUsers.Id 
                LEFT JOIN schemaName.AspNetRoles ON AspNetRoles.Id = AspNetUserRoles.RoleId
                WHERE AspNetRoles.Name = 'SuperAdmin')
END

How to set schemaName as variable so that it can be dynamic in the while loop.

You would have to use dynamic SQL.

Gordon Linoff
– Gordon Linoff

2016-05-09 11:49:17 +00:00
Commented May 9, 2016 at 11:49 — Gordon Linoff
– Gordon Linoff, Commented May 9, 2016 at 11:49

gofr1 · Accepted Answer · 2016-05-09 12:16:03Z

Something like this will help:

DECLARE @sql nvarchar(4000),
        @schemaName nvarchar(200),
        @i int = 1,
        @vParams nvarchar(100)

SET @vParams = '@uid int OUTPUT'


WHILE (@i <= (SELECT MAX(idx) FROM @schema_table))

BEGIN

    SELECT @schemaName = schemaName
    FROM @schema_table
    WHERE idx = @i


    SELECT @sql = '
                    SELECT @userid = AspNetUsers.Id
                    FROM schemaName.AspNetUsers
                    LEFT JOIN schemaNameAspNetUserRoles ON  AspNetUserRoles.UserId = AspNetUsers.Id 
                    LEFT JOIN [' +@schemaName + '].AspNetRoles ON AspNetRoles.Id = AspNetUserRoles.RoleId
                    WHERE AspNetRoles.Name = ''SuperAdmin'';'


    EXEC sp_executesql @sql, @vParams, @userid=@uid OUTPUT
    -- here you hot @userid with value you need and can do something with it
    SET @i = @i + 1

END

Collectives™ on Stack Overflow

Set Variable with select query with dynamic schema name

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related