Stored Procedure Error Question

  • Thread starter Thread starter Vayse
  • Start date Start date
V

Vayse

Using SQL2000 and Access 2000.

I'm still a bit confused about Error trapping. Below is the script for
creating a test table and stored procedure.
The parmReturn value is zero, whether the stored procedure has errors or
not. However, the TestPhone field is unchanged, so the transaction rollback
has worked.

If I try from Query Analyser
DECLARE @err INT
EXEC @err = aaaTest_SP

The message window is:
(1 row(s) affected)

Ran Part 1 successfully
Server: Msg 245, Level 16, State 1, Procedure aaaTest_SP, Line 26
Syntax error converting the varchar value 'A99' to a column of data type
int.

I would have though that 'An error has occured' would have appeared in the
message window?

Could someone enlighten me please.
Thanks
Vayse


*********************************************
Dim cmdTest As New ADODB.Command
Dim connSSP As ADODB.Connection
Dim parmReturn As ADODB.Parameter

With cmdTest
.ActiveConnection = CurrentProject.Connection
.CommandText = "aaaTest_SP"
.CommandType = adCmdStoredProc

Set parmReturn = cmdTest.CreateParameter
With parmReturn
.Name = "Ret_Value"
.Type = adInteger
.Direction = adParamReturnValue
End With
.Parameters.Append parmReturn

.Execute
End With

If parmReturn.Value = 0 Then
msgbox "Success"
Else
' Error checking
End if

-- Create Table
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[aaaTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[aaaTest]
GO

CREATE TABLE [dbo].[aaaTest] (
[TestID] [int] IDENTITY (1, 1) NOT NULL ,
[TestName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TestPhone] [int] NULL
) ON [PRIMARY]
GO

-- Stored Procedure

CREATE PROCEDURE aaaTest_SP
AS
DECLARE @err1 INT
DECLARE @err2 INT

BEGIN TRANSACTION

UPDATE aaaTest
SET TestPhone = '123'
WHERE TestID =1

SET @err1 = @@ERROR
If @err1 <> 0
BEGIN
PRINT 'An error has occured'
ROLLBACK TRANSACTION
RETURN @err1
END

ELSE
BEGIN
PRINT 'Ran Part 1 successfully'
END

UPDATE aaaTest
SET TestPhone = 'A99'
WHERE TestID =1

SET @err2 = @@ERROR
If @err2 <> 0
BEGIN
PRINT 'An error has occured'
ROLLBACK TRANSACTION
RETURN @err2
END

ELSE
BEGIN
PRINT 'Ran Part 2 successfully'
COMMIT TRANSACTION
RETURN 0
END

GO
 
Ok, I get now why 'An error has occured' message will not appear.
Which leads to other questions! :)

1)In my VB code, I check
If parmReturn.Value = 0 Then
In this case, parmReturn is zero if the SP fails.

Do the return values get set to zero if a SP fails?


2) To make sure that the SP did work correctly, is checking the connection
error collection the best way?

Thanks very much for your help.
Vayse

Sylvain Lafontaine said:
Read again the first reference:
http://www.code-magazine.com/Article.aspx?quickid=0305111

The @@error variable is only for non-fatal errors; where the instruction
doesn't abort the whole procedure. In your case, the lines 27 and after
never got executed because the error at the line 26 is a fatal error and
the SP stop right there without continuing.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


Vayse said:
Using SQL2000 and Access 2000.

I'm still a bit confused about Error trapping. Below is the script for
creating a test table and stored procedure.
The parmReturn value is zero, whether the stored procedure has errors or
not. However, the TestPhone field is unchanged, so the transaction
rollback has worked.

If I try from Query Analyser
DECLARE @err INT
EXEC @err = aaaTest_SP

The message window is:
(1 row(s) affected)

Ran Part 1 successfully
Server: Msg 245, Level 16, State 1, Procedure aaaTest_SP, Line 26
Syntax error converting the varchar value 'A99' to a column of data
type int.

I would have though that 'An error has occured' would have appeared in
the message window?

Could someone enlighten me please.
Thanks
Vayse


*********************************************
Dim cmdTest As New ADODB.Command
Dim connSSP As ADODB.Connection
Dim parmReturn As ADODB.Parameter

With cmdTest
.ActiveConnection = CurrentProject.Connection
.CommandText = "aaaTest_SP"
.CommandType = adCmdStoredProc

Set parmReturn = cmdTest.CreateParameter
With parmReturn
.Name = "Ret_Value"
.Type = adInteger
.Direction = adParamReturnValue
End With
.Parameters.Append parmReturn

.Execute
End With

If parmReturn.Value = 0 Then
msgbox "Success"
Else
' Error checking
End if

-- Create Table
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[aaaTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[aaaTest]
GO

CREATE TABLE [dbo].[aaaTest] (
[TestID] [int] IDENTITY (1, 1) NOT NULL ,
[TestName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TestPhone] [int] NULL
) ON [PRIMARY]
GO

-- Stored Procedure

CREATE PROCEDURE aaaTest_SP
AS
DECLARE @err1 INT
DECLARE @err2 INT

BEGIN TRANSACTION

UPDATE aaaTest
SET TestPhone = '123'
WHERE TestID =1

SET @err1 = @@ERROR
If @err1 <> 0
BEGIN
PRINT 'An error has occured'
ROLLBACK TRANSACTION
RETURN @err1
END

ELSE
BEGIN
PRINT 'Ran Part 1 successfully'
END

UPDATE aaaTest
SET TestPhone = 'A99'
WHERE TestID =1

SET @err2 = @@ERROR
If @err2 <> 0
BEGIN
PRINT 'An error has occured'
ROLLBACK TRANSACTION
RETURN @err2
END

ELSE
BEGIN
PRINT 'Ran Part 2 successfully'
COMMIT TRANSACTION
RETURN 0
END

GO
Click to expand...
Click to expand...
 
No idea to both of your question.

If you want to make sure that the SP did work correctly, the best way would
be to return a value different from zero, for example 1 in case of success
and -1 in case of a trappable error.

The testing, catching and correcting of most fatal errors should be part of
a normal testing & debugging procedures in the development of a database and
not part of a normal production stage.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


Vayse said:
Ok, I get now why 'An error has occured' message will not appear.
Which leads to other questions! :)

1)In my VB code, I check
If parmReturn.Value = 0 Then
In this case, parmReturn is zero if the SP fails.

Do the return values get set to zero if a SP fails?


2) To make sure that the SP did work correctly, is checking the connection
error collection the best way?

Thanks very much for your help.
Vayse

Sylvain Lafontaine said:
Read again the first reference:
http://www.code-magazine.com/Article.aspx?quickid=0305111

The @@error variable is only for non-fatal errors; where the instruction
doesn't abort the whole procedure. In your case, the lines 27 and after
never got executed because the error at the line 26 is a fatal error and
the SP stop right there without continuing.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


Vayse said:
Using SQL2000 and Access 2000.

I'm still a bit confused about Error trapping. Below is the script for
creating a test table and stored procedure.
The parmReturn value is zero, whether the stored procedure has errors or
not. However, the TestPhone field is unchanged, so the transaction
rollback has worked.

If I try from Query Analyser
DECLARE @err INT
EXEC @err = aaaTest_SP

The message window is:
(1 row(s) affected)

Ran Part 1 successfully
Server: Msg 245, Level 16, State 1, Procedure aaaTest_SP, Line 26
Syntax error converting the varchar value 'A99' to a column of data
type int.

I would have though that 'An error has occured' would have appeared in
the message window?

Could someone enlighten me please.
Thanks
Vayse


*********************************************
Dim cmdTest As New ADODB.Command
Dim connSSP As ADODB.Connection
Dim parmReturn As ADODB.Parameter

With cmdTest
.ActiveConnection = CurrentProject.Connection
.CommandText = "aaaTest_SP"
.CommandType = adCmdStoredProc

Set parmReturn = cmdTest.CreateParameter
With parmReturn
.Name = "Ret_Value"
.Type = adInteger
.Direction = adParamReturnValue
End With
.Parameters.Append parmReturn

.Execute
End With

If parmReturn.Value = 0 Then
msgbox "Success"
Else
' Error checking
End if

-- Create Table
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[aaaTest]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [dbo].[aaaTest]
GO

CREATE TABLE [dbo].[aaaTest] (
[TestID] [int] IDENTITY (1, 1) NOT NULL ,
[TestName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TestPhone] [int] NULL
) ON [PRIMARY]
GO

-- Stored Procedure

CREATE PROCEDURE aaaTest_SP
AS
DECLARE @err1 INT
DECLARE @err2 INT

BEGIN TRANSACTION

UPDATE aaaTest
SET TestPhone = '123'
WHERE TestID =1

SET @err1 = @@ERROR
If @err1 <> 0
BEGIN
PRINT 'An error has occured'
ROLLBACK TRANSACTION
RETURN @err1
END

ELSE
BEGIN
PRINT 'Ran Part 1 successfully'
END

UPDATE aaaTest
SET TestPhone = 'A99'
WHERE TestID =1

SET @err2 = @@ERROR
If @err2 <> 0
BEGIN
PRINT 'An error has occured'
ROLLBACK TRANSACTION
RETURN @err2
END

ELSE
BEGIN
PRINT 'Ran Part 2 successfully'
COMMIT TRANSACTION
RETURN 0
END

GO
Click to expand...
Click to expand...
Click to expand...
 
Back
Top