SPROC error with passing table name
-
Hi all,
I am trying to write a simple (atleast I thought so :hehe
to update an existing table with data entered from an online form. Here is what I am trying to do:create proc usp_update_emp_data
(
@tblName nvarchar(512), --Table Name
@colNameVal varchar(1024), --Column & Value pairs. Eg: Name='john',Age=32
@empID varchar(6) -- Employee ID
)
AS
Update @tblName
set @colNameVal
where emp = @sppID
GO
when I try to compile the sproc I am getting the following error:
Server: Msg 137, Level 15, State 2, Procedure usp_update_emp_data, Line 17
Must declare the variable '@tblName'.
Any ideas..??? All help will be greatly appreciated.
thanks,
V
-
You can't do this in Sql Server.
You need to either use Dynamic SQL, or create 1 stored procedure per table.
-
create proc usp_update_emp_data
(
@tblName nvarchar(512), --Table Name
@colNameVal varchar(1024), --Column & Value pairs. Eg: Name='john',Age=32
@empID varchar(6) -- Employee ID
)
AS
declare @mysql as varchar(1024)
set @mysql = 'Update ' + @tblName + ' set ' + @colNameVal + ' where emp = ' + cast( @sppID as varchar(255) )
exec @mysql
GO
Naveen
-
Hmm....well in that case will I better off writing the UPDATE query in ASP and execurting it using ADODB connection??
Also if possible can you or anyone give me an example how I can write something like this in Dynamic SQL.
thank you for all your helpful insights,
V
-
Hi Reshu,
Thanks for giving me an example. But using this code, compiles the sproc, but when I try to execute the sproc using the following query:
exec usp_update_emp_data 'emp_details', 'Name=''John'',Age=''34''', '127867'
I am getting the following error:
Could not find stored procedure 'Update emp_details set Name='John',Age='34' where emp = 127867'.
thanks again,
V
-
Hi Guys,
Just edit the sproc and make it say -
exec (@mysql)
not -
exec @mysql
Have fun
Steve
We need men who can dream of things that never were.
-
works like a charm.....thank you Steve and everyone else.
-
Please read THIS before continuing down this path. Dynamic sql should be avoided at all costs.. Especially with an asp project.
-
I hadn't noticed that.
Remi is 100% right, if its a web based project, you need to be very aware of the dangers of Dynamic SQL.
There are ways of minimising / eliminating the risks but you need to do your research before deciding that this is the best way forward.
Be afraid - be very afraid.......
Have fun
Steve
We need men who can dream of things that never were.
-
SQL injection is an issue any time you are accepting information that the user can enter (whether through form fields, or through constructing their own POST or GET command and sending it to your page). In this case, it should be possible to force the table name to be something the user cannot enter (perhaps check the name of the sending page to determine the correct table; DO NOT send the table name directly via a POST or GET).
If this is an option, then you could use sp_executesql.
Build the string for your command, but only directly append in text that is "safe"; items that the user cannot have entered arbitrary values for. For anything truly dependent on user input, go ahead and make that a parameter. When you execute the statement, you specify the parameters in a fashion very similar to that used to call a stored proc. See BOL for more details.
R David Francis
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply