Post reply

update table using parameter variable

  • December 7, 2013 at 10:30 am

    #283383

    hi

    i have a stored proc which is used to make updates and inserts to a table passed in as a string variable.

    so...

    create proc myproc (@basetable as varchar(255))

    as

    -- update

    update @baseTable

    set Quantity = s.Quantity

    from @baseTable p, @stageTbl s

    where p.TimeKey = s.TimeKey and p.AccountId = s.AccountId and p.StockId=s.StockId

    ...but i get the following error: Must declare the table variable "@baseTable"

    can i do this without using dynamic sql?

    thanks

  • December 9, 2013 at 3:33 pm

    #1672149

    No, you cannot use it like that

    you are going to have to use Dynamic SQL and exec command to accomplish this type of commands

    [font="Courier New"]Aram Koukia: http://www.koukia.ca[/font]

  • December 10, 2013 at 4:13 am

    #1672245

    DECLARE @sql varchar(max)

    SET @sql = 'Update '+ @baseTable +' set Quantity = s.Quantity

    from ' + @baseTable +' p , '+ @stageTbl +' s

    where p.TimeKey = s.TimeKey and p.AccountId = s.AccountId and p.StockId=s.StockId'

    EXEC (@sql)


    Praveen D'sa
    MCITP - Database Administrator 2008
    http://sqlerrors.wordpress.com

  • December 10, 2013 at 4:22 am

    #1672249

    Make sure you read and understand this before you try anything like that.

    John

  • December 10, 2013 at 4:30 pm

    #1672476

    PravB4u (12/10/2013)

    DECLARE @sql varchar(max)

    SET @sql = 'Update '+ @baseTable +' set Quantity = s.Quantity

    from ' + @baseTable +' p , '+ @stageTbl +' s

    where p.TimeKey = s.TimeKey and p.AccountId = s.AccountId and p.StockId=s.StockId'

    EXEC (@sql)

    Just to emphasize what John Mitchell posted above...

    This is actually some of the most dangerous code in the whole world because it concatenates user input. Please do a search on SQL Injection both in Books Online and on the web.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply