My code goes like this:
CREATE PROCEDURE ins_Fab_topc_reqs_stats
(@Subj_id numeric(13,0),
@Info_upd_id numeric (13,0),
@Info_src_cd code ,
@Sys_id varchar (15),
@Tot_topc_cnt int,
@Prcs_topc_cnt int ,
@Prcs_topc_lst varchar (500) ,
@Faild_topc_lst varchar (500),
@Err_no int OUTPUT ,
@Err_msg varchar(1000) OUTPUT
)
AS
BEGIN
/************************************************** *****
Declaration of local variables
************************************************** ******/
DECLARE @Err int
DECLARE @Err_Desc varchar(1000)
DECLARE @result int
DECLARE @TEMP_ERR INT
/************************************************** *****
Assigning default value for some local variables
************************************************** *****/
SELECT @Err = 0
SELECT @Err_Desc = NULL
SELECT @TEMP_ERR = 0
set nocount on
IF(SELECT COUNT(*)FROM Fab_topc_reqs_stats_grate WHERE SUBJ_ID = @Subj_id AND Info_upd_id = @Info_upd_id AND SYS_ID = @Sys_id)= 0
BEGIN
BEGIN TRAN
IF (SELECT COUNT(*) FROM Fab_topc_reqs_stats_grate WHERE SUBJ_ID = @Subj_id
AND Info_upd_id = @Info_upd_id AND SYS_ID = @Sys_id) = 0
BEGIN --insert begin
INSERT INTO Fab_topc_reqs_stats_grate
(Subj_id,
Info_upd_id,
Info_src_cd,
Sys_id,
Tot_topc_cnt,
Prcs_topc_cnt,
Prcs_topc_lst,
Faild_topc_lst
)
SELECT
@Subj_id,
@Info_upd_id,
@Info_src_cd,
@Sys_id,
@Tot_topc_cnt,
@Prcs_topc_cnt,
@Prcs_topc_lst,
@Faild_topc_lst
WHERE NOT EXISTS
(SELECT 1 FROM Fab_topc_reqs_stats_grate WHERE SUBJ_ID = @Subj_id AND Info_upd_id = @Info_upd_id AND SYS_ID = @Sys_id)
/***ERROR HANDLING *****/
SELECT @TEMP_ERR = @@ERROR
END --INSERT BEGIN ENDS HERE
IF @TEMP_ERR = 0
BEGIN
COMMIT TRAN
SELECT @Err = 0
SELECT @Err_Desc = 'INSERTION SUCESSFUL FOR THE SUBJ_ID : '+CONVERT(VARCHAR(50),@Subj_id)
END
ELSE
BEGIN
ROLLBACK TRAN
SELECT @Err = @TEMP_ERR
SELECT @Err_Desc = 'INSERTION FAILED FOR THE SUBJ_ID : '+CONVERT(VARCHAR(50),@Subj_id)
END
END
ELSE
BEGIN
BEGIN TRAN
UPDATE Fab_topc_reqs_stats_grate
SET Info_src_cd = @Info_src_cd,
--Sys_id = @Sys_id,
Tot_topc_cnt = @Tot_topc_cnt,
Prcs_topc_cnt = @Prcs_topc_cnt,
Prcs_topc_lst = @Prcs_topc_lst,
Faild_topc_lst = @Faild_topc_lst,
log_tmst = getdate()
WHERE SUBJ_ID = @Subj_id
AND Info_upd_id = @Info_upd_id
And SYS_ID = @sys_id
/***ERROR HANDLING *****/
IF @@ERROR = 0
BEGIN
COMMIT TRAN
SELECT @Err = 0
SELECT @Err_Desc = 'UPDATION SUCESSFUL FOR THE SUBJ_ID : '+CONVERT(VARCHAR(50),@Subj_id)
END
ELSE
BEGIN
ROLLBACK TRAN
SELECT @Err = @@ERROR
SELECT @Err_Desc = 'UPDATION FAILED FOR THE SUBJ_ID : '+CONVERT(VARCHAR(50),@Subj_id)
END
END
SELECT @Err_no=@Err
SELECT @Err_msg=@Err_desc
END
Can any one see any flaw in it?
Regards,
Subrat