Managing the errors in stored procedures

How can I manage the errors into a stored procedure?
Example:
CREATE PROCEDURE MyProcedure
@.UserName SYSNAME = NULL
AS
BEGIN
DECLARE @.v INT
SET @.v = 0
EXEC @.v = sp_helpuser @.UserName
IF (@.@.ERROR <> 0)
BEGIN
RAISERROR('My Error Message...', 16, 1)
RETURN 1
END
IF (@.v <> 0)
BEGIN
RAISERROR('Return Value is 1', 16, 1)
RETURN 1
END
RETURN 0
END
I try this example, but in case than sp_helpuser exits with error, I did
not get my error messages in my client application.
What is the correct way to manage the errors?
Thank you for help!http://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Mihaly" <Mihaly@.discussions.microsoft.com> wrote in message
news:314DE418-5F6C-4115-AB97-0B93CA56AC25@.microsoft.com...
> How can I manage the errors into a stored procedure?
> Example:
> CREATE PROCEDURE MyProcedure
> @.UserName SYSNAME = NULL
> AS
> BEGIN
> DECLARE @.v INT
> SET @.v = 0
> EXEC @.v = sp_helpuser @.UserName
> IF (@.@.ERROR <> 0)
> BEGIN
> RAISERROR('My Error Message...', 16, 1)
> RETURN 1
> END
> IF (@.v <> 0)
> BEGIN
> RAISERROR('Return Value is 1', 16, 1)
> RETURN 1
> END
> RETURN 0
> END
> I try this example, but in case than sp_helpuser exits with error, I
did
> not get my error messages in my client application.
> What is the correct way to manage the errors?
> Thank you for help!
>|||> What is the correct way to manage the errors?
I ran your proc at it seems sp_helpuser exits with return code 0 and @.@.ERROR
is also 0. However, the sp_helpuser error is raised so you should be able
to detect the error in your client app. I don't know what language/API
you're using but the VBScript example below detects the sp_helpuser error.
Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = connection
cmd.CommandType = adCmdStoredProcedure
cmd.CommandText = "MyProcedure"
Set pReturnCode = cmd.CreateParameter("@.RETURN_CODE")
pReturnCode.Type = adInteger
pReturnCode.Direction = adParamReturnValue
cmd.Parameters.Append pReturnCode
Set pLoginName = cmd.CreateParameter("@.UserName")
pLoginName.Type = adVarWChar
pLoginName.Direction = adParamInput
pLoginName.Value = "Test"
pLoginName.Size = Len(pLoginName.Value)
cmd.Parameters.Append pLoginName
On Error Resume Next 'trap Execute error
cmd.Execute
On Error GoTo 0 're-enable default error handler
MsgBox cmd.Parameters(0).Name & "=" & cmd.Parameters(0).Value
For Each error in connection.Errors
MsgBox error.Description
Next
Hope this helps.
Dan Guzman
SQL Server MVP
"Mihaly" <Mihaly@.discussions.microsoft.com> wrote in message
news:314DE418-5F6C-4115-AB97-0B93CA56AC25@.microsoft.com...
> How can I manage the errors into a stored procedure?
> Example:
> CREATE PROCEDURE MyProcedure
> @.UserName SYSNAME = NULL
> AS
> BEGIN
> DECLARE @.v INT
> SET @.v = 0
> EXEC @.v = sp_helpuser @.UserName
> IF (@.@.ERROR <> 0)
> BEGIN
> RAISERROR('My Error Message...', 16, 1)
> RETURN 1
> END
> IF (@.v <> 0)
> BEGIN
> RAISERROR('Return Value is 1', 16, 1)
> RETURN 1
> END
> RETURN 0
> END
> I try this example, but in case than sp_helpuser exits with error, I did
> not get my error messages in my client application.
> What is the correct way to manage the errors?
> Thank you for help!
>|||For what it's worth, I structure all my SPs similarly, using the following
pattern:
As
-- Declare the error handler variables ...
Declare @.Err Integer
Declare @.Msg VarChar(100)
/ *************************
In Functional part of the stored Proc
consists of blocks like the following,
(one block for each executable statement that might fail),
in each block, 3 parts,
1. set the string value of the error msg,
2. execute the statement, and then
3. test @.@.error, and if error occurred
Goto ErrHandler
********************************/
Set @.Msg = 'Failed, Unable to Insert <blank> into <blank>... '
Insert Tablename(... )
Values(x,y,z, etc. )
Set @.Err = @.@.Error If @.Err <> 0 GoTo ErrHandler
-- --
Set @.Msg = 'Failed, Unable to Update Employee %d.'
Update EMployees Set
ColumnA = @.Val1,
ColumnB = @.Val2, ...
Where EmployeeID = @.EmployeeID
Set @.Err = @.@.Error If @.Err <> 0 GoTo ErrHandler
-- --
-- ****************************************
****
-- ****************************************
****
ErrHandler:
Begin
If @.@.TranCount > 0 RollBack Transaction
If CharIndex('%d', @.Msg) > 0
Raiserror(@.Msg, 16, 1, @.EmployeeID)
Else RaisError(@.Msg, 16, 1)
If @.Err = 0 Set @.Err = -1
Return(@.Err)
End
-- ****************************************
****
/********************************
If there is more than one statement in the
Stored Proc, I create a block for each one
and put them all inside a Begin Tran Commit
Error Handler detects @.@.TranCount to
determine whether or not to RollBack...
This pattern allows you to put common
error handler at the end of all your SPs,
It Therefore keeps the code in the body
of the SP "Cleaner", making it easier to
read, and to see the SP's business structure
business process which you are implementing
********************************/
"Mihaly" wrote:

> How can I manage the errors into a stored procedure?
> Example:
> CREATE PROCEDURE MyProcedure
> @.UserName SYSNAME = NULL
> AS
> BEGIN
> DECLARE @.v INT
> SET @.v = 0
> EXEC @.v = sp_helpuser @.UserName
> IF (@.@.ERROR <> 0)
> BEGIN
> RAISERROR('My Error Message...', 16, 1)
> RETURN 1
> END
> IF (@.v <> 0)
> BEGIN
> RAISERROR('Return Value is 1', 16, 1)
> RETURN 1
> END
> RETURN 0
> END
> I try this example, but in case than sp_helpuser exits with error, I di
d
> not get my error messages in my client application.
> What is the correct way to manage the errors?
> Thank you for help!
>|||Uh Forgot You have to
Return(0)
After the Business Logic, before the ErrHandler Label, or the ErrHandler
Code will always run
-- Declare the error handler variables ...
Declare @.Err Integer
Declare @.Msg VarChar(100)
/ *************************
In Functional part of the stored Proc
consists of blocks like the following,
(one block for each executable statement that might fail),
in each block, 3 parts,
1. set the string value of the error msg,
2. execute the statement, and then
3. test @.@.error, and if error occurred
Goto ErrHandler
********************************/
Begin Transaction
Set @.Msg = 'Failed, Unable to Insert <blank> into <blank>... '
Insert Tablename(... )
Values(x,y,z, etc. )
Set @.Err = @.@.Error If @.Err <> 0 GoTo ErrHandler
-- --
Set @.Msg = 'Failed, Unable to Update Employee %d.'
Update EMployees Set
ColumnA = @.Val1,
ColumnB = @.Val2, ...
Where EmployeeID = @.EmployeeID
Set @.Err = @.@.Error If @.Err <> 0 GoTo ErrHandler
-- --
Commit Transaction
Return(0)
-- ****************************************
****
-- ****************************************
****
ErrHandler:
Begin
If @.@.TranCount > 0 RollBack Transaction
If CharIndex('%d', @.Msg) > 0
Raiserror(@.Msg, 16, 1, @.EmployeeID)
Else RaisError(@.Msg, 16, 1)
If @.Err = 0 Set @.Err = -1
Return(@.Err)
End
-- ****************************************
****
"CBretana" wrote:
> For what it's worth, I structure all my SPs similarly, using the followin
g
> pattern:
> As
> -- Declare the error handler variables ...
> Declare @.Err Integer
> Declare @.Msg VarChar(100)
> / *************************
> In Functional part of the stored Proc
> consists of blocks like the following,
> (one block for each executable statement that might fail),
> in each block, 3 parts,
> 1. set the string value of the error msg,
> 2. execute the statement, and then
> 3. test @.@.error, and if error occurred
> Goto ErrHandler
> ********************************/
> Set @.Msg = 'Failed, Unable to Insert <blank> into <blank>... '
> Insert Tablename(... )
> Values(x,y,z, etc. )
> Set @.Err = @.@.Error If @.Err <> 0 GoTo ErrHandler
> -- --
> Set @.Msg = 'Failed, Unable to Update Employee %d.'
> Update EMployees Set
> ColumnA = @.Val1,
> ColumnB = @.Val2, ...
> Where EmployeeID = @.EmployeeID
> Set @.Err = @.@.Error If @.Err <> 0 GoTo ErrHandler
> -- --
>
> -- ****************************************
****
> -- ****************************************
****
> ErrHandler:
> Begin
> If @.@.TranCount > 0 RollBack Transaction
> If CharIndex('%d', @.Msg) > 0
> Raiserror(@.Msg, 16, 1, @.EmployeeID)
> Else RaisError(@.Msg, 16, 1)
> If @.Err = 0 Set @.Err = -1
> Return(@.Err)
> End
> -- ****************************************
****
> /********************************
> If there is more than one statement in the
> Stored Proc, I create a block for each one
> and put them all inside a Begin Tran Commit
> Error Handler detects @.@.TranCount to
> determine whether or not to RollBack...
> This pattern allows you to put common
> error handler at the end of all your SPs,
> It Therefore keeps the code in the body
> of the SP "Cleaner", making it easier to
> read, and to see the SP's business structure
> business process which you are implementing
> ********************************/
>
> "Mihaly" wrote:
>