update table using parameter variable

Question

Post reply

update table using parameter variable

spin

Hall of Fame

Points: 3274
More actions
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

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

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

Aram Koukia SSCommitted Points: 1521 More actions · Answer 1

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]

PravB4u Default port Points: 1401 More actions · Answer 2

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

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

John

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

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)