To handle errors. Help!!- - 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)

To handle errors. Help!!-

Author

Topic

cesark
Posting Yak Master

215 Posts

Posted - 2003-07-10 : 13:48:14

Hello!

I need to know which is the indicator indeed that allows me to know if the action in my stored procedures has led to end correctly or has been occurred some problem. Then pass this value with a variable to my application to decide what to do about it. Now I use the function @@ERROR to know the value of error, but how can I decide on my application if the action has been led to end correctly? Many people say that if the error number is different to 0 an error has occurred, but I can not say to my application to show a message if this value is different to 0, because there are many errors are only informative, and the action has been done perfectly. I don’t understand anything!! In the other hand those numbers by itself not indicate anything, I think that the true indicator is the severity associated with those numbers. In the other hand I cannot understand why all of error numbers are duplicated two times in the table sysmessages.

What can I do?

Another question is: The errors that occur in SQL remain registered in some place? I would like be able to register it to review it later, if it is not like that, how can I know if the errors has occurred?

Thank you,
Cesar

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-10 : 17:24:32

Cómo sobre esto conseguirle comenzó, y ningún no hablo español. ¡Pero sé cómo a nosotros un traductor!


USE Northwind
GO
CREATE PROC myProc2
AS
BEGIN
DECLARE @Result int , @rc int
SELECT 1 FROM NOTABLE
SELECT @Result = @@ERROR
IF @Result <> 0 
	BEGIN SELECT @rc = -1 END 
  ELSE 
	BEGIN SELECT @rc = 0 END
END
RETURN @rc
GO

CREATE PROC myProc1 
AS
BEGIN
DECLARE @rc int
SELECT @rc = 0
EXEC @rc = myProc2
IF @rc = 0 
	BEGIN 
		SELECT 'Éxito' 
	END 
  ELSE 	
	BEGIN 
		SELECT 'Falta'
		SELECT @rc = -1 
	END
END
RETURN @rc
GO

EXEC myProc1
GO

DROP PROC myProc1
GO
DROP PROC myProc2
GO

Brett

8-)

cesark
Posting Yak Master

215 Posts

Posted - 2003-07-11 : 05:21:04

Ok, but, what numbers exactly means if the action of stored procedure has been done correctly or not?. I don’t want that my users see errors that they don’ t have to solve, user only need to know if their action has been done correctly, and if it is not, try again.

@@ERROR <> 0 not indicate if the action has been done correctly, only indicate that some error has occurred, and if this error has not affected the action I don’t want user see this error because is my problem.

Here is an example of one of my Stored Procedures:

CREATE PROCEDURE check_mail_password_inscription
@E-mail varchar(50), @Password char(10),
@Offer_num int, @Date datetime, @Motives_interest varchar(250)
As
Declare @Buyer_num int

If Exists(select E-mail from Buyers where E-mail = @E-mail
and Password = @Password)
begin

select @Buyer_num = Buyer_num
from Buyers
where E-mail = @E-mail
and Password = @Password

select @Buyer_num AS Buyer_num, 1 AS Value
INSERT INTO Inscriptions_in_offers (Offer_num, Buyer_num, Date, Motives_interest)
VALUES (@Offer_num, @Buyer_num, @Date, @Motives_interest)
end
else
begin
select 0 AS Value
end

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-11 : 09:10:23

I'm not sure I understand

quote:

In the other hand I cannot understand why all of error numbers are duplicated two times in the table sysmessages.

That's not so. Check out:



SELECT Error, Count(*) FROM master..sysmessages
GROUP BY Error
HAVING COUNT(*) > 1
GO

Also, you must do (MOO) Error Handling for EVERY SQL statement.

And there different things you need to check. I call Systemic (System errors, ones that throw an error number out to @@ERROR) and application or user errors that don't. For example:


CREATE TABLE myTable1 (col1 char(1) PRIMARY KEY, col2 char(1))
GO

DECLARE @rc int, @Result int
INSERT INTO myTable1 (col1, col2) SELECT 'a', 'z'
SELECT @rc = @@ERROR, @Result = @@ROWCOUNT
SELECT @rc, @Result
-- This will fail because of a dup key...I consider this a system error
INSERT INTO myTable1 (col1, col2) SELECT 'a', 'x'
SELECT @rc = @@ERROR, @Result = @@ROWCOUNT
SELECT @rc, @Result
SELECT * FROM master..sysmessages WHERE Error = @rc
-- This will doesn't fail, but I think it should because the row doesn't exist...I call this an application error
UPDATE myTable1 Set col2 = 'm' WHERE col1 = 'x' 
SELECT @rc = @@ERROR, @Result = @@ROWCOUNT
SELECT @rc, @Result
SELECT * FROM master..sysmessages WHERE Error = @rc
GO

DROP TABLE myTable1
GO

In either of those 2 cases, I interogate either @@ERROR or @@ROWCOUNT to make sure everything is OK. IF not I GOTO an error label and display to the user the problem.

Also you can add your own messages to sysmessages, but I prefer not to do that.

I do something like:



	Insert Into Batch_Cntl (
			  Batch_Id
			, Source
			, Source_File_Name
			, Source_File_TS)
	Select		  @Batch_Id
			, 'SmartStrm'
			, @Fn2
			, @ws_Date

	SELECT @Result_Count = @@ROWCOUNT, @error_out = @@error

	If @Error_Out <> 0
		BEGIN
			Select @Error_Loc = 35
			Select @Error_Type = 50001
  			GOTO Load_Ledger_Init_sp_Error
		END

	If @Result_Count = 0
		BEGIN
			SELECT @Error_Loc = 36
			SELECT @Error_Message = 'No Rows Inserted in to the BATCH_CNTL Table For Second Months File'
			SELECT @Error_Type = 50002
  			GOTO Load_Ledger_Init_sp_Error
		END

And then handle it in the exit:


Load_Ledger_Init_sp_Error:
Rollback TRAN

Select @Comp_Code = -1, @Comp_msg = @Error_Message, @Load_Ind = 'N'

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 Load_Ledger_Init_sp_Exit

Just a note...sending the system message is a waste of time because sql has already returned it...I'd just have to open all my code (because I'm a scrub) and take it out.

Brett

8-)

cesark
Posting Yak Master

215 Posts

Posted - 2003-07-11 : 10:54:03

Thank you Brett. I' ll see with more detail later, I' m not very sure to understand all of you have commented, because I am a beginner, but thank you for your interest.

8-)
Cesar

cesark
Posting Yak Master

215 Posts

Posted - 2003-07-11 : 10:54:03

Thank you Brett. I' ll see with more detail later, I' m not very sure to understand all of you have commented, because I am a beginner, but thank you for your interest.

8-)
Cesar

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources