locked
WHILE loop to replace data in temp table columns RRS feed

  • Question

  •  Hello,

    I have 2 temp tables

    DECLARE @Jresults TABLE
    (guest_title		varchar(10)		null,
     guest_initial		varchar(20)		null,
     guest_name			varchar(50)		null,
     email				VARCHAR(100)	null,			
     postcode			varchar(10)		null,
     gender				varchar(1)		null,
     comms				varchar(20)		null,
     intrst_name1		varchar(30)		null,
     intrst_name2		varchar(30)		null,
     intrst_name3		varchar(30)		null,
     intrst_name4		varchar(30)		null,
     intrst_name5		varchar(30)		null,
     intrst_name6		varchar(30)		null,
     intrst_name7		varchar(30)		null,
     intrst_name8		varchar(30)		null,
     intrst_name9		varchar(30)		null,
     intrst_name10		varchar(30)		null,
     intrst_name11		varchar(30)		null,
     intrst_name12		varchar(30)		null,
     intrst_name13		varchar(30)		null,
     intrst_name14		varchar(30)		null,
     intrst_name15		varchar(30)		null,
     intrst_name16		varchar(30)		null,
     intrst_name17		varchar(30)		null,
     intrst_name18		varchar(30)		null,
     intrst_name19		varchar(30)		null,
     intrst_name20		varchar(30)		null,
     intrst_name21		varchar(30)		null,
     intrst_name22		varchar(30)		null,
     intrst_name23		varchar(30)		null,
     intrst_name24		varchar(30)		null,
     intrst_name25		varchar(30)		null,
     intrst_name26		varchar(30)		null,
     intrst_name27		varchar(30)		null,
     intrst_name28		varchar(30)		null,
     intrst_name29		varchar(30)		null,
     intrst_name30		varchar(30)		null,
     intrst_name31		varchar(30)		null,
     intrst_name32		varchar(30)		null,
     intrst_name33		varchar(30)		null,
     intrst_name34		varchar(30)		null,
     intrst_name35		varchar(30)		null,
     intrst_name36		varchar(30)		null,
     intrst_name37		varchar(30)		null,
     intrst_name38		varchar(30)		null,
     intrst_name39		varchar(30)		null,
     intrst_name40		varchar(30)		null,
     arrival			datetime		null,
     departure			datetime		null,
     total_rate			numeric(10,2)	null,
     reservation_id		numeric(10,0)	null,
     gst_id				numeric(10,0)	null,
     comms_id			varchar(10)		null)
    
    
    
    
    DECLARE @intrests TABLE
     (gst_id			numeric(10,0)	null,
      intrest_id		numeric(10,0)	null,
      intrest_name		varchar(30)		null)


    the 1st has a lot of "intrst_name" columns . each of these needs to to havea value assigned to it.

    currently, this is done using

    	UPDATE @Jresults
    	SET intrst_name33 = c.intrest_name
    	from  @Jresults a, @intrests c
    	where a.gst_id = c.gst_id and c.intrest_id=33
    
    	UPDATE @Jresults
    	SET intrst_name34 = c.intrest_name
    	from  @Jresults a, @intrests c
    	where a.gst_id = c.gst_id and c.intrest_id=34
    
    
    etc


    but I am now trying to make this more...compact with a WHILE loop , but I am getting different type of error message (IE, solve one "error" and another takes its place")

    Currently , I have

    DECLARE @Counter INT 
    DECLARE @limit INT
    DECLARE @ColName nvarchar(100)
    declare @sql nvarchar (1000);
    declare @tableName nvarchar (10);
    
    SET @Counter=1
    SET @limit=40
    WHILE (@Counter <= @limit)
    BEGIN
    	SET @ColName='intrst_name'+(SELECT CONVERT(varchar(2),@Counter))
    	-- Add intrests (no of intrest found in INTEREST table)
    	set @sql = N'UPDATE @Jresults set ' + @ColName + '= c.intrest_name from @Jresults a, @intrests c
    	where a.gst_id = c.gst_id and c.intrest_id='+(SELECT CONVERT(varchar(2),@Counter))+';'
    	print @sql
    	exec(@sql)
       SET @Counter  = @Counter  + 1
    END


    but it i get  the message

    Msg 1087, Level 15, State 2, Line 1
    Must declare the table variable "@Jresults".


    the @sql looks like 

    UPDATE @Jresults set intrst_name1= c.intrest_name from @Jresults a, @intrests c
    	where a.gst_id = c.gst_id and c.intrest_id=1;

    NB: number does change each time.

    I have put the temp table names in []

    	set @sql = N'UPDATE [@Jresults] set ' + @ColName + '= c.intrest_name from [@Jresults] a, [@intrests] c
    	where a.gst_id = c.gst_id and c.intrest_id='+(SELECT CONVERT(varchar(2),@Counter))+';'
    

    but then get

    Msg 208, Level 16, State 1, Line 1
    Invalid object name '@Jresults'.

    Please can someone tell/show me what I need to to iterate though the loop  to change the intrst_name1,intrst_name2, etc till the limit is reached, that will use the temp tables for the data ?

    Many Thanks in advance

    Tuesday, April 28, 2020 5:11 PM

Answers

  • You cannot use a table variable in that context.  You will need to change @Jresults to a temp table #Jresults.  That will fix your issue.

    • Marked as answer by G-Oker Tuesday, April 28, 2020 5:53 PM
    Tuesday, April 28, 2020 5:30 PM

All replies

  • You cannot use a table variable in that context.  You will need to change @Jresults to a temp table #Jresults.  That will fix your issue.

    • Marked as answer by G-Oker Tuesday, April 28, 2020 5:53 PM
    Tuesday, April 28, 2020 5:30 PM
  • HI Tom, thanks for getting back so fast. Sorry to be a dummy, but, how would I do that  ? 
    Tuesday, April 28, 2020 5:36 PM
  • I think I understand what you mean now. 

    create table #Jresults
    (guest_title		varchar(10)		null,
     guest_initial		varchar(20)		null,
     guest_name			varchar(50)		null,

    and 

    create table  #glotmpintrests 
     (gst_id			numeric(10,0)	null,
      intrest_id
    
    
    etc

    and then 

    	set @sql = N'UPDATE #glotmpJresults set ' + @ColName + '= c.intrest_name from #glotmpJresults  a, #glotmpintrests c
    	where a.gst_id = c.gst_id and c.intrest_id='+(SELECT CONVERT(varchar(2),@Counter))+';'
    

    THANK YOU

    Tuesday, April 28, 2020 5:53 PM