Update Procedure Error Recovery

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)

Update Procedure Error Recovery

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2003-04-09 : 22:44:59

UPDATE Users
SET Email = @Email, Username = @Email
WHERE UserID = @UserID
SET @ERR = @@ERROR
IF @ERR <> 0
SET @OutputMessage = 'E-mail: "' + IsNull(@Email, 'none specified') + '" is already in use.'

If @Email above exists for another record, the Update fails (as it should), but I don't get control, and the procedure aborts without testing for the error and setting the @OutputMessage string.

Is there a way I can recover in this condition so I can return a valid error message or do I need to test for the existence of a duplicate before performing the UPDATE?

Sam

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-04-09 : 23:00:00

If you really want to control it, then you need to test for the dupe before or during the UPDATE. You can also try this:

UPDATE Users
SET Email=@Email, UserName=@Email
WHERE UserID=@UserID
AND NOT EXISTS(SELECT * FROM Users WHERE UserName=@Email OR Email=@Email)
IF @@ROWCOUNT=0 RAISERROR(IsNull(@Email+' is already in use', 'No Email specified'), 16, 1)

That will throw an actual error message that you can capture through any client interface.

Edited by - robvolk on 04/09/2003 23:02:04

SamC
White Water Yakist

3467 Posts

Posted - 2003-04-10 : 00:15:41

Thanks Rob,

I took your feedback and after careful thought, rewrote it using an IF EXISTS. Somehow, it sunk in later that the single statement you had did everything mine did without the IF THEN ELSE.

I made one refinement to allow users to revise their own email with the same email (for example to change case) like this:

AND NOT EXISTS (... WHERE (Email=@Email OR Username=@Email) AND UserID<>@UserID)

-------------------
I'll have to try RAISERROR sometime. I've been working with OUTPUT parameter strings that describe the error in layman's terms. RAISERROR may require less coding since an OUTPUT parameter doesn't need to be declared and passed.

I need to understand how to distinguish RAISED errors (in ASP) from unintended errors. I need to display RAISERRORS differently.

Sam

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-04-10 : 02:25:47

Hi Sam:

Good suggestion from rob, but if you really want to retain control of the proc after the error, add "SET XACT_ABORT OFF" before the statement. With the default value of ON, if an error occurs, the entire transaction is rolled back and the proc exits.

OS

X002548
Not Just a Number

15586 Posts

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

I don't know...I wouldn't mess with settings...there's no need to.

First of all, you didn't mention why it failed. You must have a unique Index or Contraint on your Email Column. Also I've never had an instance where I couldn't trap an error...SQL will still raise it's own error in addition...but I do the following (any comments appreciated):

Interogation of a SQL Statement, 1 for business logic error, 1 for Systemic...like:

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

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

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

Then a standard exit routine:

Load_Ledger_Init_sp_Exit:

--Select @Command_String = 'BULK INSERT Tax_Load_Log '
-- + 'FROM '
-- + '''' + 'd:\Data\Tax\log_out.txt' + ''''
-- + ' WITH (FIELDTERMINATOR = ' + '''' + '|' + '''' + ',ROWTERMINATOR = '
-- + '''' + '\n' + ''''
-- +',FIRSTROW =1)'
--Select @Command_String

SET @cmd = 'bcp ' + @db_name + '..Tax_Load_Log in d:\Data\Tax\log_out.txt -t"|" -c -S' + @@servername + ' -Utaxuser -Ptaxuser'
SET @Command_string = 'EXEC master..xp_cmdshell ''' + @cmd + ''''
Select @Command_string

Exec(@Command_String)

Update System_Var
Set Load_Ind = @Load_Ind
-- ,System_State = @System_State
,Load_Message = RTrim('INIT|')+@Comp_Msg
,Updated_By = 'INITLOAD'
,Updated_Ts = getdate()

SET NOCOUNT OFF

return @Comp_Code

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

SET @var = RTrim(Convert(Char(20),@Log_Id)) + '|U|'+RTrim(Convert(char(50),GetDate(),109))+'|Load Completed UnSuccessfully'
SET @cmd = 'echo ' + '"|' + @var + '|"' + ' >> d:\Data\Tax\log_out.txt'
SET @Command_string = 'EXEC master..xp_cmdshell ''' + @cmd + ''', NO_OUTPUT'

Exec(@Command_String)

SET @var = RTrim(Convert(Char(20),@Log_Id)) + '|U|'+RTrim(Convert(char(50),GetDate(),109))+'|'+RTrim(@Error_Message)+''
SET @cmd = 'echo ' + '"|' + @var + '|"' + ' >> d:\Data\Tax\log_out.txt'
SET @Command_string = 'EXEC master..xp_cmdshell ''' + @cmd + ''', NO_OUTPUT'

Exec(@Command_String)

DECLARE USER_MSG CURSOR READ_ONLY FOR
select distinct user_id from user_work_profile(NoLock)
Where Work_Type = 'A'
-- Where User_Id = 'x057117'
OPEN USER_MSG

FETCH NEXT FROM USER_MSG
INTO @User_Id

WHILE @@FETCH_STATUS = 0

BEGIN
Select @Command_String = 'Exec master..xp_cmdshell "NET SEND ' + @User_Id + ' TaxReconDB Load Failed", no_output '
Select @Command_String
Exec (@Command_String)

FETCH NEXT FROM USER_MSG
INTO @User_Id
END

CLOSE USER_MSG
DEALLOCATE USER_MSG

RAISERROR @Error_Type @Error_Message

GOTO Load_Ledger_Init_sp_Exit

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources