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.
See Question&Answers more detail:os