How to LOOP a query that is created dynamically in SQL Server

Question

I have a stored procedure where I am getting the database name from a table and then trying to create a dynamic query from this database name and fetching the results. Once the results are fetched I need to loop these results for further queries to be executed to get the desired result

USE DATABASE1
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [User].[update_client_details] 
AS
    DECLARE @clientdata CURSOR,
            @clientid INT,
            @SQL NVARCHAR(2000),
            @uid INT,
            @isFirst INT,
            @isTemp INT,
            @inactive INT,
            @createdDate Date

BEGIN
    DECLARE C CURSOR LOCAL FOR 
        SELECT clientuserid FROM USER.queen_client

    OPEN C
    FETCH NEXT FROM C INTO @clientid

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @SQL = N'SELECT userid, @isFirst=isfirst, @isTemp=istemp, @inactive=inactive, @createdDate=createddate FROM ' +QUOTENAME(@clientid)+'.USER.queen_user;';

        EXEC sys.sp_executesql @SQL, N'@inactive int OUTPUT, @uid int OUTPUT, @isFirst int OUTPUT, @isTemp int OUTPUT, @createdDate date OUTPUT', @inactive OUTPUT, @uid OUTPUT, @isFirst OUTPUT, @isTemp OUTPUT, @createdDate OUTPUT;

        // @SQL returns multiple rows - I need to loop the output of @SQL 
        // UPDATE QUERY BASED ON IF CONDITION COMES HERE

        FETCH NEXT FROM C INTO @clientid
    END

    CLOSE C
    DEALLOCATE C
END

As the SQL query is dynamic - how do I loop the output of this dynamic query.

That dynamic statement isn't the query I gave you before, nor would that dynamic statement work, you can't return a dataset in the same query you assign values to variables. Plus you don't define `@isFirst` in `sp_executesql`. — Thom A, Jun 09 '20 at 14:47
@Larnu yes I modified the fields. Then How do I loop the dataset? — Sai sri, Jun 09 '20 at 14:58

David Browne - Microsoft · Answer 1 · 2020-06-09T14:15:53.023

As the SQL query is dynamic How do I loop the output of this dynamic query.

Create a temp table outside of the dynamic query, and insert into it in the dynamic query. Then you can read from the temp table.

    SET @SQL = N'
INSERT INTO #tempUser(userId,IsFirst,IsTemp,inactive,createddate)
SELECT userid, isfirst, istemp, inactive, createddate 
FROM ' +QUOTENAME(@clientid)+'.USER.queen_user;';

But a better overall approach might be to create a partitioned view in a seperate database over all the tables. EG

create view queen_user
as
select 123 clientId, userid, isfirst, istemp, inactive
from Client123.USER.queen_user
union all
select 124 clientId, userid, isfirst, istemp, inactive
from Client124.USER.queen_user
union all
. . .
union all
select 999 clientId, userid, isfirst, istemp, inactive
from Client999.USER.queen_user

And have a procedure that alters it any time a new client db is added.

I dont need to create a new table. I already have a table Also I am getting the results. But I want to know how to loop those results — Sai sri, Jun 09 '20 at 14:23
That's a view, not a table. And the first part of the answer tells you how to do exactly that: to loop over the results of a dynamic query, insert into a temp table first, and then loop over that. — David Browne - Microsoft, Jun 09 '20 at 14:29

How to LOOP a query that is created dynamically in SQL Server

1 Answers1

Linked