While loop inside Dynamic SQL query

Question

I have a requirement wherein inside a dynamic sql query i want to execute another dynamic query in a while loop

PFB the code for which i am facing issues, also note that parts of the code in the outer dynamic query has been removed as those are working properly, facing issues with the code that is written inside the while loop:

declare @createindex table  
(id int identity(1,1),  
indexscript varchar(max))  
  
insert into @createindex values ('Create Index Index1 on table1 (Col1)')  
insert into @createindex values ('Create Index Index2 on table1 (Col2)')  
  
select * from @createindex  
  
declare @countindex int  
declare @count int  
set @countindex = (select count(1) from @createindex)  
set @count = 1  
declare @indexscript varchar(max)  
  
DECLARE @SQL VARCHAR(max)  
SET @SQL='  
while ('+convert(varchar(12),@count)+'<='+convert(varchar(12),@countindex)+')  
begin  
  
set'+ @indexscript+' = (select indexscript from'+  @createindex+'  where id ='+ convert(varchar(12),@count)+')  
  
exec ('+@indexscript+')  
  
set'+ convert(varchar(12),@count)+'='+convert(varchar(12),@count+1)+'  
  
end'  
  
exec @SQL  

Error:
Msg 137, Level 16, State 1, Line 23
Must declare the scalar variable "@createindex".

Answer 1

Hi @Priya Jha
When you EXEC with a string, the command is carried out in a new session, so variables cannot be used pass arguments or get results. However, you could create a temporary table and use this table inside the dynamic statement, like this:

CREATE TABLE #createindex (id int identity(1,1), indexscript varchar(max));  
INSERT INTO #createindex VALUES ('Create Index Index1 on table1 (Col1)')  
INSERT INTO #createindex VALUES ('Create Index Index2 on table1 (Col2)')     
   
DECLARE @SQL VARCHAR(max)  
SET @SQL='  
 declare @countindex int  
 declare @count int  
 set @countindex = (select count(1) from #createindex)  
 set @count = 1  
 declare @indexscript varchar(max)  
  
 while (@count <=@countindex)  
 begin  
 set @indexscript = (select indexscript from #createindex where id =@count)  
 exec (@indexscript)   
 set @count =@count+1   
 end'  
PRINT @SQL      
EXEC (@SQL)  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

If you print your @alenzi you will find your error.

You cannot pass a table variable that way. You will need to use sp_executesql and a variable to pass the contents of the table.

Answer 3

Writing complex dynamic SQL like this requires a structured approach. I am not going to explain it here, because I have already have a long article about dynamic SQL on my web site: https://www.sommarskog.se/dynamic_sql.html.

When it comes to crafting dynamic SQL like this, there are rarely any shortcuts. That is, attempts to make shortcuts, often ends in tears, grey hairs, and eventually leads to questions like this.

It is better to accept that dynamic SQL is an advanced feature and let it take the time it takes.

Answer 4

Did you try print @alenzi to see what do you get as your final script?

I think this complex script is unlikely to execute, but you also need exec (@alenzi ). @alenzi in parentesis.