I am creating a stored procedure which contains two while loops consequently, the below mentioned is used for creating few temp tables and the next loop writes some data to it.
The temp table name is retrieved from a static table
Issue: the below while loop after the very first iteration and goes to the next loop(second one). Am I doing something wrong here?
Or is this how while loops works in SQL Server (Azure SQL MI) within a procedure?
Also is it a good practice to create temp tables in this way?
declare @b varchar(100)
declare @bmax varchar(100)
declare @inpt_tbl_nm varchar(100)
declare @inpt_tbl_sql varchar(1000)
set @b = 1
select @bmax = max(RwId) from eds_int.abc
while @b < = @bmax
begin
select @inpt_tbl_nm = otpt_tmp_tbl from eds_int.abc where Rwid=@b
select @inpt_tbl_sql = 'create table ' + @inpt_tbl_nm + '(RowId INT IDENTITY(1,1), SchemaName sysname, TableName nvarchar(150), query nVARCHAR(4000), condition nVARCHAR(4000))'
exec (@inpt_tbl_sql)
set @b = @b + 1
end
DECLARE @a INT
DECLARE @amax INT
DECLARE @max INT
SELECT @a = 1, @amax = MAX(RwId) from eds_int.abc
WHILE @a <= @amax
BEGIN
.
.
.
.
END
Edit:
declare @b int
declare @bmax int
declare @inpt_tbl_nm varchar(100)
declare @inpt_tbl_sql varchar(1000)
declare @test_query varchar(1000)
set @b = 1
select @bmax = 1
while @b < = @bmax
begin
print @b
select @inpt_tbl_nm = '#test_delete'
select @inpt_tbl_sql = 'create table ' + @inpt_tbl_nm + '(RowId INT IDENTITY(1,1), SchemaName sysname, TableName nvarchar(150), query nVARCHAR(4000), condition nVARCHAR(4000))'
exec (@inpt_tbl_sql)
print @inpt_tbl_sql
select @test_query = 'select * from ' + @inpt_tbl_nm
exec (@test_query)
set @b = @b + 1
end
Error: Msg 208, Level 16, State 0, Line 1 Invalid object name '#test_delete'.
