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, December 15, 2014 4:30 AM
Points: 121, Visits: 429
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
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, April 30, 2014 10:27 AM
Points: 1,001, 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: Friday, September 5, 2014 6:19 AM
Points: 147, Visits: 591
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: Tuesday, December 23, 2014 9:32 AM
Points: 5,490, Visits: 10,354
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 @ 9:11 AM
Points: 35,808, Visits: 32,483
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