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.
Also tried the following. Keep getting message
"tbl_Clients_ClientWorkStatus.Client_PK_ID
cannot be bound."
TIA.
Bill
Code:
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
"tbl_Clients_ClientWorkStatus.Client_PK_ID
cannot be bound."
Code:
--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