April 17, 2008 at 2:37 am
Does anyone have a template for what they consider is the perfect update stored proc? Say for a fictitious customer table?
Would love to see how the experts do it.
April 17, 2008 at 3:00 am
matthew (4/17/2008)
Does anyone have a template for what they consider is the perfect update stored proc? Say for a fictitious customer table?Would love to see how the experts do it.
There's no such thing as a "single" template for an update stored proc. Each update is different, has different business rules and requirements. Some updates are straight-forward, single-table updates. Some updates require joins to other tables. And some updates can't be performed without some condition being satisfied beforehand.
It's a bit like asking a writer what their template is for the perfect novel.
The only template that I use for all stored procs is the formatting of the code & commenting; the use of set nocount on for all procs; and try...catch statements where necessary (which I generally only use when dealing with transactions).
April 17, 2008 at 3:13 am
So you don't do a try - catch when only updating one table? What does your try-catch block look like when you do use one?
Also, do you use timestamps?
April 17, 2008 at 3:31 am
matthew (4/17/2008)
So you don't do a try - catch when only updating one table? What does your try-catch block look like when you do use one?Also, do you use timestamps?
Typically I don't use try-catch statements when just updating one table. If there's an error then SQL Server will auto-rollback the transaction and our application will handle the error in the application code.
There are exceptions to this, where we might want to continue doing some processing on the SQL Server if there's been an error but not typically.
Here's a typical try-catch block we use:
BEGIN TRY
BEGIN TRANSACTION;
--code
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
DECLARE @error_message VARCHAR(510);
SELECT @error_message = error_message();
RAISERROR(@error_message,16,1);
RETURN error_number();
END CATCH
The error number associated with the error message will be 50000, which the application handles differently to other error numbers. Note though that the return code for the stored procedure would be the original error number.
We sometimes do other stuff or we do it differently but that's the starting point.
April 17, 2008 at 6:09 am
Huh, I'd assume a try catch even on a single row single table update. Even there you could run into deadlocks, whatever, that you want to handle within the TSQL rather than simply returning an error to the proc.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 17, 2008 at 7:52 am
Thanks chaps.
Not sure how you'd tackle a deadlock. Can you retry within the same SP somehow?
No one's mentioned timestamps.
April 17, 2008 at 8:11 am
Straight out of the BOL:
USE AdventureWorks;
GO
-- Declare and set variable
-- to track number of retries
-- to attempt before exiting.
DECLARE @retry INT;
SET @retry = 5;
-- Keep attempting to update
-- table if this task is
-- selected as the deadlock
-- victim.
WHILE (@retry > 0)
BEGIN
BEGIN TRY
BEGIN TRANSACTION;
UPDATE my_sales
SET sales = sales + 1
WHERE itemid = 1;
WAITFOR DELAY '00:00:13';
UPDATE my_sales
SET sales = sales + 1
WHERE itemid = 2;
SET @retry = 0;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
-- Check error number.
-- If deadlock victim error
-- then reduce retry count
-- for next update retry.
-- If some other error
-- occurred then exit
-- retry WHILE loop.
IF (ERROR_NUMBER() = 1205)
SET @retry = @retry - 1;
ELSE
SET @retry = -1;
-- print error information
EXECUTE usp_MyErrorLog;
IF XACT_STATE() <> 0
ROLLBACK TRANSACTION;
END CATCH;
END; -- end while loop
GO
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 17, 2008 at 8:28 am
That's excellent, thanks.
You've got WAITFOR in there, is that just for illustration? Otherwise it could go in the catch block where you decrement the retry count presumably?
April 17, 2008 at 8:40 am
It's sample code from the BOL.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 17, 2008 at 9:03 am
Grant Fritchey (4/17/2008)
Huh, I'd assume a try catch even on a single row single table update. Even there you could run into deadlocks, whatever, that you want to handle within the TSQL rather than simply returning an error to the proc.
Deadlocks??? What are those?? We never get any deadlocks - EVER!:D
We handle deadlocks on the application for some reason - was there before I started. Truth is we don't actually get very many deadlocks so it's not a big issue. Good point though.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply