SPROC error with passing table name

Question

Post reply

SPROC error with passing table name

vijju2k

SSCommitted

Points: 1531
More actions
February 7, 2005 at 9:55 am

#65526

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

Viewing 10 posts - 1 through 9 (of 9 total)

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

PW-201837 SSC-Insane Points: 20805 More actions · Answer 1

You can't do this in Sql Server.

You need to either use Dynamic SQL, or create 1 stored procedure per table.

Naveen Mehta SSC-Addicted Points: 497 More actions · Answer 2

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

vijju2k SSCommitted Points: 1531 More actions · Answer 3

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

vijju2k SSCommitted Points: 1531 More actions · Answer 4

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

Ste_P SSCarpal Tunnel Points: 4430 More actions · Answer 5

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.

vijju2k SSCommitted Points: 1531 More actions · Answer 6

works like a charm.....thank you Steve and everyone else.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

Please read THIS before continuing down this path. Dynamic sql should be avoided at all costs.. Especially with an asp project.

Ste_P SSCarpal Tunnel Points: 4430 More actions · Answer 8

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.

RD Francis SSCarpal Tunnel Points: 4867 More actions · Answer 9

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