Stored Procedure label - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Stored Procedure label

Author

Topic

Vivaldi
Constraint Violating Yak Guru

298 Posts

Posted - 2003-02-14 : 12:03:07

Well,

I have seen some sprocs where they
have their

sql

and a label at the bottom to handle the error handling.

I am trying to write more robust sprocs that have error handling
at any point and when an error occurs to jump out of the sql area
hit the label (then do rollbacks, etc).

Are labels the way to go. Or is there a better solution?

Does the label get read if it comes to the label area
in the sproc, or must it be called explicitly?

Thanks for any advice

________________________________________________
A recent study shows that Southern Germany may have the best beer in the world.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-02-14 : 12:15:38

Well I have always heard from different experienced programmers that using labels is kind of like cheating. But that doesn't mean that you shouldn't use them. I currently do not have any stored procedures with labels in them, but I would never say that I will not use them in the future.

Example of what to do:

BEGIN TRAN
UPDATE Table1
SET Column1 = 'value'
WHERE Column3 = 'other'

IF @@ERROR = 0
COMMIT TRAN
ELSE
BEGIN
ROLLBACK TRAN
GOTO BailOut
END

DELETE FROM Table2
WHERE Column2 = 76

:BailOut

RETURN

--
This example shows that if the UPDATE statement fails, the DELETE statement will not occur due to the BailOut label.

Vivaldi
Constraint Violating Yak Guru

298 Posts

Posted - 2003-02-14 : 12:22:17

Thanks,

So, is the best way to write code to always
do the
if(@@error <> 0)
-- do the rollback
else
-- commit it

?
It just seems there could be a more usable way to utilize error handling in a sproc than tripling the size of your code with
the error handlers.

bitte

________________________________________________
A recent study shows that Southern Germany may have the best beer in the world.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-02-15 : 02:21:34

You might want to set @context before the updates then the error handling becomes a single goto.

declare @error int, @rowcount int
declare @Context varchar(100)

update...
select @error = @@error, @rowcount = @@rowcount
if @error <> 0
begin
set @context = 'update 1'
goto errhnd
end

begin tran
update...
select @error = @@error, @rowcount = @@rowcount
if @error <> 0
begin
set @context = 'update 2'
goto errhnd
end

commit tran
return

ErrHnd:
raiserror('failed error=%d - %s',16,-1,@error, @context)
return

ErrHndTran:
rollback tran
raiserror('failed error=%d - %s',16,-1,@error, @context)
return

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-02-15 : 11:31:46

oooh, nr, I like that idea ! Hadn't thought of that one ... also kind of labels each segment of your code without needing a comment! very nice, I am going to start using this...

- Jeff

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2003-02-18 : 10:37:54

We use labels all the time for our error handling in our procedures. I'm not an advocate of GOTO's in any language, but they do have their place.

Vivaldi
Constraint Violating Yak Guru

298 Posts

Posted - 2003-02-18 : 10:49:14

Wow,

Thanks for the advice.

I will dig in and utilize that.

Good to know its a reasonable way to do that.

________________________________________________
A recent study shows that Southern Germany may have the best beer in the world.

X002548
Not Just a Number

15586 Posts

Posted - 2003-02-18 : 14:44:06

There is a new feature with SQL 2000...it comes with sample templates so you don't have to type standard DDL code (like create Table, ect).

It comes paramterized so you can globally replace them by going to menu item "EDIT\Replace Template Parameters", or CTRL+Shft+M.

This is very useful, but they also allow you to create your own templates....so I took the sample below and turned in to a template.

It needs to be stored in the location:

C:\Program Files\Microsoft SQL Server\80\Tools\Templates\SQL Query Analyzer\Create Procedure

The following can be placed in a file and saved as *.tql:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[<stored_procedure_name, sysname, usp_New>]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[<stored_procedure_name, sysname, usp_New>]
GO

CREATE PROCEDURE <stored_procedure_name, sysname, usp_New>
AS
--
-- Enterprise Solutions
--
-- File: <location_of_script, varchar(255), C:\TEMP\>
-- Date: <Creation_Date, Date, GetDate()>
-- Author: <author_name, varchar(255), Brett Kaiser>
-- Server: <server_name, varchar(255), NJROS1D151\NJROS1D151DEV>
-- Database: <db_Name, varchar(255), TaxReconDB>
-- Login: <Login, varchar(255), sa>
-- Description: <short_desc, varchar(255), This Procedure will >
--
--
-- The stream will do the following:
--
-- <Functions, varchar(255), '1. Function...>

--
-- Tables Used: <Tables_Used, varchar(255), Table>
--
-- Tables Created: <Tables_Created, varchar(255), Table>
--
--
-- Row Estimates:
-- name rows reserved data index_size unused
-- -------------------- ----------- ------------------ ------------------ ------------------ ------------------
--
--
-- sp_spaceused tblAcct_LedgerBalance

--Change Log
--
-- UserId Date Description
-- ----------- -------------- ------------------------------------------------------------------------------------------
-- <Author_Id, Varchar(8), x002548> <Install_Date, DateTime, GetDate> <Init_Rel, varchar(255), 1. Initial release>
--
--
--

Declare @error_out int, @Result_Count int, @Error_Message varchar(255), @Error_Type int, @Error_Loc int

BEGIN TRAN

<SQL_Statement, varchar(255), SELECT>

Select @Result_Count = @@ROWCOUNT, @error_out = @@error

If @error_out <> 0
BEGIN
Select @Error_Loc = 1
Select @Error_Type = 50001
GOTO <stored_procedure_name, sysname, usp_New>_Error
END

If @Result_Count = 0 -- Business Logic Error...This one for an expected row but no results
BEGIN
Select @Error_Loc = 1
Select @Error_Message = 'Put appropriate message here'
Select @Error_Type = 50002
GOTO <stored_procedure_name, sysname, usp_New>_Error
END

COMMIT TRAN

<stored_procedure_name, sysname, usp_New>_Exit:

-- Place any house keeping procedures here like...

Set ansi_warnings ON

return

<stored_procedure_name, sysname, usp_New>_Error:

Rollback TRAN

If @Error_Type = 50001
BEGIN
Select @error_message = (Select 'Location: ' + ',"' + RTrim(Convert(char(3),@Error_Loc))
+ ',"' + ' @@ERROR: ' + ',"' + RTrim(Convert(char(6),error))
+ ',"' + ' Severity: ' + ',"' + RTrim(Convert(char(3),severity))
+ ',"' + ' Message: ' + ',"' + RTrim(description)
From master..sysmessages
Where error = @error_out)
END
If @Error_Type = 50002

BEGIN
Select @Error_Message = 'Location: ' + ',"' + RTrim(Convert(char(3),@Error_Loc))
+ ',"' + ' Severity: UserLevel '
+ ',"' + ' Message: ' + ',"' + RTrim(@Error_Message)
END

RAISERROR @Error_Type @Error_Message

GOTO <stored_procedure_name, sysname, usp_New>_Exit

GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources