How To Loop A Query That Is Created Dynamically In Sql Server

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 res

Solution 1:

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

createview queen_user
asselect123 clientId, userid, isfirst, istemp, inactive
from Client123.USER.queen_user
unionallselect124 clientId, userid, isfirst, istemp, inactive
from Client124.USER.queen_user
unionall
. . .
unionallselect999 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.