|
 |
asp_databases thread: @@Error
Message #1 by rg1@h... on Mon, 23 Apr 2001 23:59:11
|
|
Hi.
I have the following SQL 7.0 Stored Procedure. If the row I'm trying to
update does not exist, the statements between the "If @@error <> 0" BEGIN
and END do not get executed! Any suggestions as to why not?
CREATE PROCEDURE sGROUPUpdateTest
(
@GroupName varchar(25),
@BuildPPONetworkID char(2),
@BuildVersion varchar(6),
@ActiveStatus char(1))
AS
BEGIN
BEGIN TRAN
DECLARE @ErrMess varchar(200)
UPDATE sGROUP
SET BuildPPONetworkID = @BuildPPONetworkID,
BuildVersion = @BuildVersion,
ActiveStatus = @ActiveStatus
WHERE GroupName = @GroupName
IF @@error <> 0
BEGIN
ROLLBACK TRAN
SET @ErrMess = ' Stored Procedure
sGROUPUpdate1: GROUP not found - value = ' + @GroupName
RAISERROR (@ErrMess,11,1)
RETURN(2)
END
COMMIT TRAN
RETURN(0)
END
Message #2 by Peter Zahos <pzahos@s...> on Tue, 24 Apr 2001 09:12:08 +0800
|
|
Grab your UPDATE statement with all the parameters and run it in Query
Analyzer. My guess is that your statement comes up with "(0 row(s)
affected)" and not an error.
Let us know how you handle this.
-----Original Message-----
From: rg1@h... [mailto:rg1@h...]
Sent: Tuesday, 24 April 2001 7:59 AM
To: ASP Databases
Subject: [asp_databases] @@Error
Hi.
I have the following SQL 7.0 Stored Procedure. If the row I'm trying to
update does not exist, the statements between the "If @@error <> 0" BEGIN
and END do not get executed! Any suggestions as to why not?
CREATE PROCEDURE sGROUPUpdateTest
(
@GroupName varchar(25),
@BuildPPONetworkID char(2),
@BuildVersion varchar(6),
@ActiveStatus char(1))
AS
BEGIN
BEGIN TRAN
DECLARE @ErrMess varchar(200)
UPDATE sGROUP
SET BuildPPONetworkID = @BuildPPONetworkID,
BuildVersion = @BuildVersion,
ActiveStatus = @ActiveStatus
WHERE GroupName = @GroupName
IF @@error <> 0
BEGIN
ROLLBACK TRAN
SET @ErrMess = ' Stored Procedure
sGROUPUpdate1: GROUP not found - value = ' + @GroupName
RAISERROR (@ErrMess,11,1)
RETURN(2)
END
COMMIT TRAN
RETURN(0)
END
Message #3 by "Darin Strait" <dstrait@e...> on Mon, 23 Apr 2001 22:15:22 -0400
|
|
If nothing in a table is updated due to your WHERE conditions, this is not
an error. Suggest you do something like
DIM @error integer, @rows_affected integer
UPDATE (stuff)
SELECT @error = @@ERROR, @rows_affected = @@ROWCOUNT
If @error <> 0
begin
-- error handling goes here
end
If @rows_affected = 0
begin
-- no rows to update handling goes here
end
HTH,
Darin Strait, MS SQL Server Development and Administration
http://home.earthlink.net/~dstrait/professional/resume.htm
----- Original Message -----
From: <rg1@h...>
To: "ASP Databases" <asp_databases@p...>
Sent: Monday, April 23, 2001 11:59 PM
Subject: [asp_databases] @@Error
> Hi.
>
> I have the following SQL 7.0 Stored Procedure. If the row I'm trying to
> update does not exist, the statements between the "If @@error <> 0" BEGIN
> and END do not get executed! Any suggestions as to why not?
>
> CREATE PROCEDURE sGROUPUpdateTest
> (
> @GroupName varchar(25),
> @BuildPPONetworkID char(2),
> @BuildVersion varchar(6),
> @ActiveStatus char(1))
> AS
> BEGIN
> BEGIN TRAN
>
> DECLARE @ErrMess varchar(200)
>
> UPDATE sGROUP
> SET BuildPPONetworkID = @BuildPPONetworkID,
> BuildVersion = @BuildVersion,
> ActiveStatus = @ActiveStatus
> WHERE GroupName = @GroupName
>
> IF @@error <> 0
> BEGIN
> ROLLBACK TRAN
> SET @ErrMess = ' Stored Procedure
> sGROUPUpdate1: GROUP not found - value = ' + @GroupName
> RAISERROR (@ErrMess,11,1)
> RETURN(2)
> END
>
> COMMIT TRAN
>
> RETURN(0)
> END
>
Message #4 by Rita Greenberg <rg1@h...> on Tue, 24 Apr 2001 07:45:11 -0700
|
|
Thanks Darin. I never looked at it that way!
-----Original Message-----
From: Darin Strait [mailto:dstrait@e...]
Sent: Monday, April 23, 2001 7:15 PM
To: ASP Databases
Subject: [asp_databases] Re: @@Error
If nothing in a table is updated due to your WHERE conditions, this is not
an error. Suggest you do something like
DIM @error integer, @rows_affected integer
UPDATE (stuff)
SELECT @error = @@ERROR, @rows_affected = @@ROWCOUNT
If @error <> 0
begin
-- error handling goes here
end
If @rows_affected = 0
begin
-- no rows to update handling goes here
end
HTH,
Darin Strait, MS SQL Server Development and Administration
http://home.earthlink.net/~dstrait/professional/resume.htm
----- Original Message -----
From: <rg1@h...>
To: "ASP Databases" <asp_databases@p...>
Sent: Monday, April 23, 2001 11:59 PM
Subject: [asp_databases] @@Error
> Hi.
>
> I have the following SQL 7.0 Stored Procedure. If the row I'm trying to
> update does not exist, the statements between the "If @@error <> 0" BEGIN
> and END do not get executed! Any suggestions as to why not?
>
> CREATE PROCEDURE sGROUPUpdateTest
> (
> @GroupName varchar(25),
> @BuildPPONetworkID char(2),
> @BuildVersion varchar(6),
> @ActiveStatus char(1))
> AS
> BEGIN
> BEGIN TRAN
>
> DECLARE @ErrMess varchar(200)
>
> UPDATE sGROUP
> SET BuildPPONetworkID = @BuildPPONetworkID,
> BuildVersion = @BuildVersion,
> ActiveStatus = @ActiveStatus
> WHERE GroupName = @GroupName
>
> IF @@error <> 0
> BEGIN
> ROLLBACK TRAN
> SET @ErrMess = ' Stored Procedure
> sGROUPUpdate1: GROUP not found - value = ' + @GroupName
> RAISERROR (@ErrMess,11,1)
> RETURN(2)
> END
>
> COMMIT TRAN
>
> RETURN(0)
> END
>
Message #5 by Rita Greenberg <rg1@h...> on Tue, 24 Apr 2001 07:55:29 -0700
|
|
Hi Peter.
I did exactly what you suggested and it came back with no error - just that
it had completed! I received an explanation for this from Darin. The WHERE
clause does not return a error if the condition is not met.
I can handle it one of two ways. Either in the ASP page to try to find the
record first before updating and then error out if the recordset is empty
(it's an internal web site so I don't have to worry about speed). Or handle
it in the Stored Procedure. Darin's email has the code there. I think I'll
probably do the latter. Seems more efficient.
Rita
-----Original Message-----
From: Peter Zahos [mailto:pzahos@s...]
Sent: Monday, April 23, 2001 6:12 PM
To: ASP Databases
Subject: [asp_databases] RE: @@Error
Grab your UPDATE statement with all the parameters and run it in Query
Analyzer. My guess is that your statement comes up with "(0 row(s)
affected)" and not an error.
Let us know how you handle this.
-----Original Message-----
From: rg1@h... [mailto:rg1@h...]
Sent: Tuesday, 24 April 2001 7:59 AM
To: ASP Databases
Subject: [asp_databases] @@Error
Hi.
I have the following SQL 7.0 Stored Procedure. If the row I'm trying to
update does not exist, the statements between the "If @@error <> 0" BEGIN
and END do not get executed! Any suggestions as to why not?
CREATE PROCEDURE sGROUPUpdateTest
(
@GroupName varchar(25),
@BuildPPONetworkID char(2),
@BuildVersion varchar(6),
@ActiveStatus char(1))
AS
BEGIN
BEGIN TRAN
DECLARE @ErrMess varchar(200)
UPDATE sGROUP
SET BuildPPONetworkID = @BuildPPONetworkID,
BuildVersion = @BuildVersion,
ActiveStatus = @ActiveStatus
WHERE GroupName = @GroupName
IF @@error <> 0
BEGIN
ROLLBACK TRAN
SET @ErrMess = ' Stored Procedure
sGROUPUpdate1: GROUP not found - value = ' + @GroupName
RAISERROR (@ErrMess,11,1)
RETURN(2)
END
COMMIT TRAN
RETURN(0)
END
|
|
|
View Cart
