Stored Procedure

Question

Stored Procedure

Tomas Haglund

Mr or Mrs. 500

Points: 551
More actions
January 12, 2005 at 7:01 am

#85340

Hello! I would like to use a SP to update tables like Update tablename SET... But it would be nice to use parameter for the table name like UPDATE @table SET ... so I can use SP for several tables.
This generate an error, is it posible to do this ??

Tomas

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

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

Charles Otten Default port Points: 1475 More actions · Answer 1

It is possible to do it but you would be taking a performance hit in order to do so. You either write all the code within the proc and only execute the proper code based on the parameter passed or you create an SQL statement within the proc and then execute the SQL use the exec command. Neither of these is a best practice. While saving a little time in development and coding you are adding complexity to the procedure and you are causing a large performance hit since the procedure will have to be recompiled everytime it runs. My suggestion is to create a single procedure for each table you need to update which simplifies maintenance in the future and also simplifies readability of both the SQL code and the front end development code. Just my two cents.

If the phone doesn't ring...It's me.

Sam Shiell Ten Centuries Points: 1373 More actions · Answer 2

Hi

Just to expand on what Charles says, you cannot do anything like

SELECT * FROM @TableName .....

If you insist the way to do this is something like (..doing this from memory so syntax of the EXECUTE may be a bit off):

----------------------------------

DELCLARE @String AS VARCHAR(500)

SET @String = 'SELECT * FROM ' + @TableName + '....'

EXECUTE (@String)

---------------------------

As Charles says I wouldn't recommend this for a number of reasons, mainly that performance would be poor as it isn't pre-compiled as a stored proc is...

Hope that helps

Sam

Tomas Haglund Mr or Mrs. 500 Points: 551 More actions · Answer 3

Thank's ! Think a do one sp for every table.

Regards

Tomas

Antares686 SSC Guru Points: 125444 More actions · Answer 4

Couple of things here.

1) You will need to add the WITH RECOMPILE option to the SP to ensure it never tries to use a stored execution plan for performance reasons.

2) The fact you are doing this dynamically thru the SP means you will have to give update rights to all the tables which is what using an SP is to avoid after performance.

3) You now open yourself to potential injection attacks.

Suggestions

1) Write seperate stored procedures and stop being lazy (sorry may sound rude and harsh but that really is the only justification for not doing it right (it will take a lot of time, this is what you are saying when you do the way you suggest), have been there myself).

a) Protect access to your tables.

b) Stored execution plans can increase performance.

c) Central coding for updates.

2) Use views instead of tables for the table access, but this is more work.

a) Can protect sensitive columns from destruction as the view only needs update rights then

3) Do everything client side (or web side depending on the app)

a) Can check table variable for unsafe data.

b) Usign ADO command object will allow you to parameterize the rest of the inputs to reduce potential injection possiblities and enfore strict typing of the data inputs before the attempt on the SQL Side.

Again this isn't meant to be rude or harsh, but like I have learned myself it is sometimes better to hear the facts and truth and face what I am doing wrong than to go on doing it repeatedly.