No error if target record does not exists. 2

[willydude]

I have created the following delete sproc. If the target record exists, it is deleted. But I am not getting any error msg if the target record does not exist.

Use ClientPayroll
GO

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[Clients_Delete_ClientWorkStatusInfo]
(@ClientNumber varchar(5),
@Client_PK_ID int output,
@Message varchar (20) output
)
as

SET NOCOUNT ON

declare
@Continue bit
set @Continue = 0
set @Client_PK_ID = 0
set @Message = ''

if @ClientNumber is null or len(@ClientNumber) <> 4
begin
set @Message = 'Valid Client Number Required'
set @Continue = 0
end

BEGIN Transaction Delete_WorkStatus

---need select here for Client_PK_ID
Select @Client_PK_ID =
		tbl_Clients_ClientBasicInfo.Client_PK_ID
From tbl_Clients_ClientBasicInfo
where 
tbl_Clients_ClientBasicInfo.ClientNumber = @ClientNumber

delete from tbl_Clients_ClientWorkStatus
where tbl_Clients_ClientWorkStatus.Client_PK_ID =
			@Client_PK_ID

--check for errors. Not telling me if record does not exist.
IF @@Error <> 0
	Begin  -- rollback the trans
	Rollback transaction Delete_WorkStatus
	raiserror ('Delete Failed.', 18,1)
	return 99
	end

commit transaction Delete_WorkStatus

return 0

Also tried the following. Keep getting message
"tbl_Clients_ClientWorkStatus.Client_PK_ID
cannot be bound."

--determine if selected Client_PK_ID is in WorkStatus tbl. If it is
-- delete record. If record does not exist in Status tbl, tell me.

IF tbl_Clients_ClientWorkStatus.Client_PK_ID = @Client_PK_ID
	Begin	
	delete from tbl_Clients_ClientWorkStatus
	where tbl_Clients_ClientWorkStatus.Client_PK_ID =
			@Client_PK_ID
	set @Message = 'Record Deleted.'
	END
ELSE
	begin
	set @Message = 'Record Does Not Exists.'
	set @Continue = 0
	end
end

TIA.

Bill

 

[bborissov]

If you want to know if some record exist in a table you should use EXISTS:

---need select here for Client_PK_ID
Select @Client_PK_ID =
        tbl_Clients_ClientBasicInfo.Client_PK_ID
From tbl_Clients_ClientBasicInfo
where
tbl_Clients_ClientBasicInfo.ClientNumber = @ClientNumber

IF EXISTS(SELECT *
                 FROM tbl_Clients_ClientWorkStatus
           where Client_PK_ID = @Client_PK_ID)
   BEGIN
        delete from tbl_Clients_ClientWorkStatus
               where tbl_Clients_ClientWorkStatus.Client_PK_ID = @Client_PK_ID
   END
ELSE 
   --- There is no record in tbl_Clients_ClientWorkStatus

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[SQLDenis]

In addition to what Boris said, you need to check @@Error after EVERY statement and store it in a variable

read

http://www.sommarskog.se/error-handling-II.html

and

http://www.sommarskog.se/error-handling-I.html

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[willydude]

Thanks guys!

 

[pjw001]

Strictly speaking you have not had an error. SQL Server does not know how many rows you expect to have selected/updated/deleted so if 0 rows are affected this is just as valid as 1 or 1000.

 

[willydude]

Everything I’ve seen so far re: errors, has to do with inserting data, not deleting. With a valid code (A), I get my @Message ‘Record Deleted.’ which makes sense given where the @Message is located. Even though no record will be actually deleted due to a constraint issue.

But it does not give me the desired @Message when an error 547 (as an example below) is encountered. Though my results pane does tell me that there is a constraint problem. Which it should. No PRINTs are working.

With an invalid code (Q), I get the message “Msg 266, Level 16, State 2, Procedure Admin_Delete_EthnicityCode, Line 0
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 2, current count = 3.”

I have tried many, many different configurations trying to get this to work.

Is the following a candidate for TRY/Catch?

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[Admin_Delete_EthnicityCode]
(@EthnicityCode varchar(3) output,  --not a PK_ID
@Message varchar (20) output
)
as
SET NOCOUNT ON

declare @Continue bit 
declare @Error int

set @Continue = 0
set @Message = ''

if @EthnicityCode is null or len(@EthnicityCode) > 2
begin
set @Message = 'Valid Ethnicity Code Required'
set @Continue = 0
end

BEGIN Transaction 
---need select here for EthnicityCode
Select @EthnicityCode =
	tbl_Admin_EthnicityCodes.EthnicityCode
From tbl_Admin_EthnicityCodes
where 
tbl_Admin_EthnicityCodes.EthnicityCode = @EthnicityCode
-----------------------
IF EXISTS(SELECT EthnicityCode 
FROM tbl_Admin_EthnicityCodes
where EthnicityCode = @EthnicityCode)
--I tried putting the error handling in the following block.

Begin
delete from tbl_Admin_EthnicityCodes
where tbl_Admin_EthnicityCodes.EthnicityCode = 
	@EthnicityCode
commit transaction  
Set @Message = 'Record Deleted.'
End  

ELSE 
-- What if a valid code was not entered? 
 --need to know immediately if code does not exists
begin
set @Message = 'Code Does Not Exist.'
set @Continue = 0
end

select @Error = @@ERROR
IF @Error ! = 0  ---error somewhere
begin
IF @Error = 547  --Ref Integrity issue

Begin
PRINT 'Referential Integrity Constraint Problem.'
PRINT 'Ethnicty Code Being Used In Related Table.'
End

ELSE  --other error has occurred

Begin
Print 'Unknown Error.'
Print 'Error Number is ' + Convert(Varchar, @Error)
End

Rollback Transaction
--	set @Message = 'Record Does Not Exists.'
--	set @Continue = 0
END

return @Error