Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

update table using parameter variable Expand / Collapse
Author
Message
Posted Saturday, December 7, 2013 10:30 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 14, 2014 8:25 AM
Points: 116, Visits: 402
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
Post #1520826
Posted Monday, December 9, 2013 3:33 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, April 30, 2014 10:27 AM
Points: 939, Visits: 160
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



Aram Koukia: http://www.koukia.ca
Post #1521327
Posted Tuesday, December 10, 2013 4:13 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 7:21 AM
Points: 147, Visits: 589
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
Post #1521446
Posted Tuesday, December 10, 2013 4:22 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:31 AM
Points: 5,242, Visits: 9,497
Make sure you read and understand this before you try anything like that.

John
Post #1521451
Posted Tuesday, December 10, 2013 4:30 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:53 PM
Points: 36,795, Visits: 31,257
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1521712
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse