How to run a while loop to run a dynamic query in SQL?

Question

Declare @var1 nvarchar(max)
Declare @var2 nvarchar(max)
Declare @var3 nvarchar(max)

While loop ( @var1, @var2, @var3 .... )
 Begin  
     insert into TableA
     exec (@var1) .. - in the loop logic
 End

Yeah , but passing the variable & running the exec var1 .. exec @var2 in the sequence .. — goofyui
– goofyui, Commented Apr 11, 2019 at 5:46
This is a dynamic query execution, not just while loop .. I need to fetch each variables to the exec syntax — goofyui
– goofyui, Commented Apr 11, 2019 at 5:47
Yes please.. fetch each variable & pass it to the exec syntax inside the loop — goofyui
– goofyui, Commented Apr 11, 2019 at 5:52

Daniel Brughera · Accepted Answer · 2019-04-11 06:43:54Z

1

If I understood well, you wan to use a loop to execute a set of dynamic SQL queries. Then you need to use a table to iterate between them, Im using a variable table, but you can use a temp table if you prefer

DECLARE @Vars TABLE([ID] NOT NULL IDENTITY, [Var] NVARCHAR(MAX))
/*
INSERT Values to @Vars
*/
DECLARE @ID INT, @Var NVARCHAR(MAX)
WHILE EXISTS (SELECT 1 FROM @Vars)
BEGIN
    SELECT TOP 1 @ID = [ID], @Var = [Var] FROM @Vars ORDER BY [ID]

    INSERT TableA
    EXEC(@Var)

    DELETE @Vars WHERE [ID] = @ID
END

edited Apr 11, 2019 at 6:43

answered Apr 11, 2019 at 6:34

Daniel Brughera

1,6511 gold badge9 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to run a while loop to run a dynamic query in SQL?

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