Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2006
    Posts
    16

    Unanswered: Temp table issue

    Hi
    I am using a temp table to insert some values. Now based on those values in the temp table I am trying to update my original permanent table. Everything seems right as far as the values for the variables are concerned but the update is not happening. It is correct syntactically also but the update is not happening. I am new to programming and not sure if I can assign one variable to another even if they hold specific values.

    select i_stage into #temp_stage1 from riv_stage_ac where @var1 is null
    update riv_stage_ac set @var1=@var2 where i_stage in (select i_stage from #temp_stage1)

    @var1 holds the column name 'allow_buy' and @var2 holds 'Y'

    Any help will be highly appreciated!Thanks!

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    No you cannot specify a variable column name in this way, you’ll need to use dynamic sql but take care that you don’t open yourself for hacking via SQL injection

    If @var1 is unset your select will get all records as it translates to
    select i_stage into #temp_stage1 from riv_stage_ac where null is null
    which evaluates to true for all records and if it is set to ‘allow_buy’ as you say then there should be no records in the temp table as the value ‘allow_buy’ is not a null value

    And in the update; If there are 20 records that qualify your where then your update will 20 times set the variable @var1 to ‘Y’, assuming @var2 is set to ‘Y’

    You don’t need a temp table; here is some code for you to play with.
    Code:
    create table #riv_stage_ac (rivkey int, i_stage int, allow_buy char(1) null)
    insert into #riv_stage_ac values (1,1,'N')
    insert into #riv_stage_ac values (2,1,null)
    insert into #riv_stage_ac values (3,2,null)
    insert into #riv_stage_ac values (4,1,'Y')
    insert into #riv_stage_ac values (5,3,'N')
    declare @var1 varchar(10), @var2 varchar(1)
    select * from #riv_stage_ac where @var1 is null -- all records
    select @var1='allow_buy', @var2='Y'
    select * from #riv_stage_ac where @var1 is null -- no records
    exec ('select * from #riv_stage_ac where '+@var1+' is null') -- wat you wanted
    -- and the update without a temp table
    exec ('
      update #riv_stage_ac set '+@var1+'='''+@var2+''' 
      from #riv_stage_ac a, #riv_stage_ac b 
      where a.i_stage=b.i_stage
        and b.'+@var1+' is null
         ')
    select * from #riv_stage_ac order by rivkey

  3. #3
    Join Date
    Apr 2006
    Posts
    16
    Hi thanks for the reply. It works fine and I also realised how careful one has to be with dynamic sql.

    One question why are there extra quotes-
    update #riv_stage_ac set '+@var1+'='''+@var2+'''
    from #riv_stage_ac a, #riv_stage_ac b
    where a.i_stage=b.i_stage
    and b.'+@var1+' is null

  4. #4
    Join Date
    Apr 2006
    Posts
    16
    Also how do I add another where clause say where feed_cd like 'EAGLE'?

    How do I handle the quotes to put this in the dynamic sql and is there a good source to study dynamic sql?

  5. #5
    Join Date
    Jan 2003
    Location
    Geneva, Switzerland
    Posts
    353
    You just need to build the entire SQL statement as a string assigned to a variable, and then execute that string:
    Code:
    declare @cmd varchar(200)
    select @cmd = 'update foo set bar='+@value+' where baz="junk"'
    select @cmd = @cmd + 'and bork=1234'
    
    exec (@cmd)
    Michael

  6. #6
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Quote Originally Posted by memonks
    why are there extra quotes-
    update #riv_stage_ac set '+@var1+'='''+@var2+'''
    That is a quote within a quote
    e.g.
    Code:
    1> select 'john''s car'
    2> go
                
     ---------- 
     john's car
    Quote Originally Posted by memonks
    how do I add another where clause say where feed_cd like 'EAGLE'
    Just add it into the sql string
    Code:
    exec ('
      update ....
      where a.i_stage=b.i_stage
        and b.'+@var1+' is null
        and b.feed_cd like ''EAGLE%''
         ')
    or if you don't use quoted_identifier
    then you can mix double and single quotes as in example from Michael

  7. #7
    Join Date
    Apr 2006
    Posts
    16

    Thanks

    Thanks guys for your help. I figured it out in the end with all the examples provided by you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •