error-checking all in one place

In my SQL 2000 stored proc I have a few dozen 'UPDATE mytable...'
statements, all updating the same table. I'd like to do error-checking
for these UPDATE statements all in one place, and I figured I'd create a
separate proc. I found out quickly that this is a bad idea, but my plan
was to pass two args to the other proc: (1) the column name and (2) the
desired new value. For all sorts of reasons, this fails badly. What's
the best way to do this? Thanks.Rick Charnes (rickxyz--nospam.zyxcharnes@.thehartford.com) writes:
> In my SQL 2000 stored proc I have a few dozen 'UPDATE mytable...'
> statements, all updating the same table. I'd like to do error-checking
> for these UPDATE statements all in one place, and I figured I'd create a
> separate proc. I found out quickly that this is a bad idea, but my plan
> was to pass two args to the other proc: (1) the column name and (2) the
> desired new value. For all sorts of reasons, this fails badly. What's
> the best way to do this? Thanks.
Error-check after each statement. Error-checking in SQL 2000 is a tedious
task. It's a lot better in SQL 2005.
For some more detailed tips I have an article on my web site:
http://www.sommarskog.se/error-handling-II.html.
And don't pass column or table names as parameters. That will leave you
in a quagmire of dynamic SQL.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||>> I have a few dozen 'UPDATE mytable...' <<
Try to combine the UPDATEs into one statement (or as few as possible)
using CASE expressions, subqueries, etc. Constraints on the base table
should be doing the data scrubbing for you.|||Thanks for this excellent article. So there's no way (other than using
dynamic SQL) that will allow me to put my UPDATE statements in a
separate object where I can then do my error-checking? (Sorry if I'm
being repetitive!)
In article <Xns97D35E5686AD1Yazorman@.127.0.0.1>, esquel@.sommarskog.se
says...
> Rick Charnes (rickxyz--nospam.zyxcharnes@.thehartford.com) writes:
> Error-check after each statement. Error-checking in SQL 2000 is a tedious
> task. It's a lot better in SQL 2005.
> For some more detailed tips I have an article on my web site:
> http://www.sommarskog.se/error-handling-II.html.
> And don't pass column or table names as parameters. That will leave you
> in a quagmire of dynamic SQL.
>
>|||Rick Charnes (rickxyz--nospam.zyxcharnes@.thehartford.com) writes:
> Thanks for this excellent article. So there's no way (other than using
> dynamic SQL) that will allow me to put my UPDATE statements in a
> separate object where I can then do my error-checking? (Sorry if I'm
> being repetitive!)
Well,
UPDATE tbl
SET col1 = CASE @.column WHEN 'col1' THEN @.value ELSE col1 END,
col2 = CASE @.column WHEN 'col1' THEN @.value ELSE col2 END,
..
But if the columns are different data type, you will have to deal
with that in the CASE statement. You could pass @.value as sql_variant,
and have an explicit convert in each CASE.
But by now, having multiplied error-checking is starting to sound really
palatable.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx