June 12, 2002 at 3:03 pm
I'd like to be able to create an update stored procedure that has the following characteristics:
Does not use dynamic SQL.
Will only update the attributes that changed.
In PL-SQL you can use Bind Variables to achieve this, but I cannot determine if there is a way to do it in T-SQL. Has anyone else struggled with this?
For those interested, here is the problem that prompted this:
**ONLY READ THE BELOW IF YOU REALLY WANT TO**
Ordinarily, I use static Stored Procedures for update procedures in T-SQL as they are more efficient to run, even though all variables have to be provided, not just the ones that are changing. In this one particular database, we're being 'forced' to use a particular data model. In it, they have an organization table for holding information on things such as schools, hospitals, companies, etc. there is also a set of locator tables that hold postal, physical and tele locator information. There is a derived many-to-many relationship between organization and these locator tables. However, they have denormalized the primary address and phone number information so that it also appears in the organization table. Only the primary address and phone are also stored here, but there are also to be locator records with the same information. Keeping it in synch is going to be a bear. The problem is that only some of the attributes that appear in the locator tables also appear in the organization table. I want to be able to create an update procedure for the organization table that will also allow the associated locator tables for the primary address and phone to be updated at the same time. I have update procedures for the locators that I want to call from the organization update procedure to do this, but consider the following scenario:
- Organization is added and primary address and phone locator records are also created.
- An attribute that does not also appear in the organization table is modified in the primary tele locator (i.e. e-mail address).
- Core Organization information is then updated using the update SP. Since email address does not appear in the Organization table, I would have to look it up first before performing the update to the tele locator with the same information.
I'm pretty sure I didn't do a very good job of explaining this. I've come up with other ways of solving the problem, but none are as elegant as a update procedure that does not use dynamic SQL and updates only the changed values. Can anyone help?
June 13, 2002 at 4:26 am
Hi , in a trigger you can use the old and new value of a column, maybe this is of some help.
June 13, 2002 at 4:56 am
We've got a couple good articles here on the site about dynamic sql and how to achieve the "almost same" results with static code. Works but not pretty in my opinion. Maybe Im not clear on the problem, but isn't it more to do with updating several sorta related tables rather than updating/not updating a column in a table if it has changed/not?
As mentioned earlier, a trigger does offer one method, if you have SQL2K I'd say an instead of trigger might be an even better alternative, since you get to decide what changes propagate. It's probably flawed in this case, since sometimes you WANT to be able to update columns even if to reset to their current values.
Andy
June 24, 2002 at 9:52 am
Sorry I didn't get back to this right away, I was called off on another assignment and didn't have time. Thanks for your responses, though.
Unfortunately, since we're writing against a model that is being used by other applications (COTS type stuff), so we cannot use triggers. Everything has to be done in SPs so as not to stomp on the COTS code.
While the problem does involve updating several sort-related tables, I don't want to have to update all the tables unless I absolutely have to. Also, the Organization table has 30+ attributes and a similar table has 60+ attributes. My programmers are not going to be happy if they have to provide values for each of these attributes (if they exist) everytime they call the update procedure.
I'll take a look for the articles you mentioned. We can try dynamic and see how that works, I guess.
Thanks!
-Ken
June 24, 2002 at 9:59 am
Not pretty, but I'd create several stored procedures to handle the cases. Then use a master stored procedure to accept the parameters and then "call" the appropriate sp(s) for that case. Can be amaintenance issue if schema and/or business rules change often. But if not, it provides a buffer for your developers, but also encapsualtes logic using SPs without dynamic sql.
Steve Jones
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply