0

I have a stored procedure called R_Proc with a parameter called id. Inside I am creating temp table ##Insert_Data for inserting data:

set @Sql_Create_Table = 'CREATE TABLE ##Insert_Data...'
exec sp_executesql @Sql_Create_Table

Then I have created a cursor for selecting data based on the id:

declare cur cursor for
    select name from test where id = @id

open cur

fetch next from cur into @name

while @@FETCH_STATUS = 0
begin
    set @Sql_Search_Table = 'select id from ##Insert_Data where ....'
    exec sp_executesql @Sql_Search_Table

    --I want to get **id from above exec** and pass to another statement below
    set @Sql_Table = 'insert into ##Insert_Data
                          select name from tbl where id=<id from above>'
end

Last attempt was :

set @Sql_Search_Table ='select '+@id+'=id from ##Insert_Data where ....'
exec sp_executesql @Sql_Search_Table
set @temp = @id

--I want to get **id from above exec** and pass to another statement below
set @Sql_Table = 'insert into ##Insert_Data
                      select name from tbl where id='+@temp+'

When I print @temp variable the value is null.

I am stuck. How can I do something like this?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Nick
  • 3
  • 3
  • 4
    This feels like multiple [XY Problems](http://xyproblem.info). Why are you using a dynamic statement? Whybie that dynamic statement written with a huge security vulnerability in it? Why are you using a (global) temporary table to perform an `INSERT`? Why are you using a `WHILE` when SQL is a set based language and performs awfully at iteration. Take a step back and tell us what you are *really* trying to do as it appears you've gone done multiple wrong rabbits holes here. – Thom A Feb 21 '21 at 11:26
  • Thank you for your answer. I can see now clearly that i am doing it wrong. I just figured it out that i have done it very complex. I was start written the stored procedure last night till morning. I am rewriting the whole proccess. – Nick Feb 21 '21 at 11:34
  • Is this whole thing not just `select name into ##Insert_Data from tbl` or something like that? What are you *actually* trying to do? – Charlieface Feb 21 '21 at 12:48

0 Answers0