dynamic sql

Question

Post reply

dynamic sql

Sridhar-137443

Hall of Fame

Points: 3762
More actions
March 26, 2008 at 7:26 am

#183635

Hi,
I need to write several update statements in a stored procedure. the update statement will be like
update table1
set field1 = @newvalue
where field1= @oldvalue.
I have to run this update statement on different fields. so I was thinking of passing the fieldname as a parameter to the stored procedure and construct the dynamic sql based on that. I can do this easily. I do not want to write separate procedures for each field because of the maintenance issues. If I need to make a change to these procedures there is a chance that we might forget to make a change on one of the stored procedures. That is why I am avoiding to write different stored procedures.
Is there any other way that I can do to achieve this functionality? like is it possible to use SMO to do this or is there something else in sql server 2005 that we could use?
Please let me know.
Thanks,
sridhar.

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

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

SMO would just slow you down and I don't think you need real dynamic SQL.

Why can't you just populate a table with the Old/New values and do a simple joined update?

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Jones - SSC Editor SSC Guru Points: 736210 More actions · Answer 2

How many procedures are we talking about? the maintenance issues are also better security practices. If you can't do what Jeff suggested, put a comment in each procedure listing all the other procedures and make them separat.e

Sridhar-137443 Hall of Fame Points: 3762 More actions · Answer 3

Hi Jeff,

can you please explain how we could do this using a join?

I am thinking that the new table will have fields (oldvalue, newvalue, fieldname). but when I join to the table that I need to do update, I will still need the dynamic sql. right?

Thanks,

sridhar.

Sridhar-137443 Hall of Fame Points: 3762 More actions · Answer 4

For now I think we will have 11 procedures if we do a separate procedure for each field. so I am not sure if it is the right way to have 11 procedures that have the same functionality except with different field.

Thanks,

sridhar.