invalid value 0 for state. valid range is from 1 to 127 while running stored procedure??
Procedure :
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spXConfirmEnteredRates]
(
@SRTHRTId int,
@SRTHPRHIndOvrId varchar(10),
@SRTHPRIndOvrTotalTax decimal(4,2),
@SRTHPRIndOvrNetTax decimal(4,2),
@SRTHEffectiveDateOfStart Varchar(50),
@SRTHStatus char(1),
@SRTDQualificationCode varchar(10),
@SRTDExpertiseCode varchar(3),
@SRTMDTransactionId int,
@SRTMDPayLevelId int,
@SRTMDPayLevelDesc varchar(20),
@SRTMDShiftTypeCode varchar(2),
@SRTMDShiftPeriodCode int,
@SRTMDBaseRate decimal(6,2),
@SRTMDWeeKDayMon decimal(6,2),
@SRTMDWeeKDayTue decimal(6,2),
@SRTMDWeeKDayWed decimal(6,2),
@SRTMDWeeKDayThu decimal(6,2),
@SRTMDWeeKDayFri decimal(6,2),
@SRTMDWeeKEndSat decimal(6,2),
@SRTMDWeekEndSun decimal(6,2),
@SRTMDPubWeekDays decimal(6,2),
@SRTMDPubWeekEnds decimal(6,2),
@SRTMDMarkUpBaseRate decimal(6,2),
@SRTMDMarkUpWeeKDayMon decimal(6,2),
@SRTMDMarkUpWeeKDayTue decimal(6,2),
@SRTMDMarkUpWeeKDayWed decimal(6,2),
@SRTMMarkUpWeeKDayThu decimal(6,2),
@SRTMDMarkUpWeeKDayFri decimal(6,2),
@SRTMDMarkUpWeeKEndSat decimal(6,2),
@SRTMDMarkUpWeekEndSun decimal(6,2),
@SRTMDMarkUpPubWeekDays decimal(6,2),
@SRTMDMarkUpPubWeekEnds decimal(6,2),
@SRTMDClientBaseRate decimal(6,2),
@SRTMDClientWeeKDayMon decimal(6,2),
@SRTMDClientWeeKDayTue decimal(6,2),
@SRTMDClientWeeKDayWed decimal(6,2),
@SRTMDClientWeeKDayThu decimal(6,2),
@SRTMDClientWeeKDayFri decimal(6,2),
@SRTMDClientWeeKEndSat decimal(6,2),
@SRTMDClientWeekEndSun decimal(6,2),
@SRTMDClientPubWeekDays decimal(6,2),
@SRTMDClientPubWeekEnds decimal(6,2),
@User VARCHAR(10),
@SRTDDSDayCode_Mon varchar(5),
@SRTDShiftType_Mon varchar(2),
@SRTDStarttime_Mon time,
@SRTDEndtime_Mon time,
@SRTDDaySplitTime_Mon time,
@SRTDMaxShEndTime_Mon time,
@SRTDMaxShHours_Mon decimal(3,1),
@SRTDOvrtimePerc_Mon decimal(4,2),
@SRTDMinMealHrs_Mon decimal(2,1),
@SRTDMealBrkMins_Mon int,
@SRTDIsRateBaseOrDayBasis_Mon bit,
@SRTDRate_Mon decimal(6,2),
@SRTDRateTimes_Mon decimal(5,2),
@Isdefault_Mon bit,
@SRTDDSDayCode_Tue varchar(5),
@SRTDShiftType_Tue varchar(2),
@SRTDStarttime_Tue time,
@SRTDEndtime_Tue time,
@SRTDDaySplitTime_Tue time,
@SRTDMaxShEndTime_Tue time,
@SRTDMaxShHours_Tue decimal(3,1),
@SRTDOvrtimePerc_Tue decimal(4,2),
@SRTDMinMealHrs_Tue decimal(2,1),
@SRTDMealBrkMins_Tue int,
@SRTDIsRateBaseOrDayBasis_Tue bit,
@SRTDRate_Tue decimal(6,2),
@SRTDRateTimes_Tue decimal(5,2),
@Isdefault_Tue bit,
@SRTDDSDayCode_Wed varchar(5),
@SRTDShiftType_Wed varchar(2),
@SRTDStarttime_Wed time,
@SRTDEndtime_Wed time,
@SRTDDaySplitTime_Wed time,
@SRTDMaxShEndTime_Wed time,
@SRTDMaxShHours_Wed decimal(3,1),
@SRTDOvrtimePerc_Wed decimal(4,2),
@SRTDMinMealHrs_Wed decimal(2,1),
@SRTDMealBrkMins_Wed int,
@SRTDIsRateBaseOrDayBasis_Wed bit,
@SRTDRate_Wed decimal(6,2),
@SRTDRateTimes_Wed decimal(5,2),
@Isdefault_Wed bit,
@SRTDDSDayCode_Thu varchar(5),
@SRTDShiftType_Thu varchar(2),
@SRTDStarttime_Thu time,
@SRTDEndtime_Thu time,
@SRTDDaySplitTime_Thu time,
@SRTDMaxShEndTime_Thu time,
@SRTDMaxShHours_Thu decimal(3,1),
@SRTDOvrtimePerc_Thu decimal(4,2),
@SRTDMinMealHrs_Thu decimal(2,1),
@SRTDMealBrkMins_Thu int,
@SRTDIsRateBaseOrDayBasis_Thu bit,
@SRTDRate_Thu decimal(6,2),
@SRTDRateTimes_Thu decimal(5,2),
@Isdefault_Thu bit,
@SRTDDSDayCode_Fri varchar(5),
@SRTDShiftType_Fri varchar(2),
@SRTDStarttime_Fri time,
@SRTDEndtime_Fri time,
@SRTDDaySplitTime_Fri time,
@SRTDMaxShEndTime_Fri time,
@SRTDMaxShHours_Fri decimal(3,1),
@SRTDOvrtimePerc_Fri decimal(4,2),
@SRTDMinMealHrs_Fri decimal(2,1),
@SRTDMealBrkMins_Fri int,
@SRTDIsRateBaseOrDayBasis_Fri bit,
@SRTDRate_Fri decimal(6,2),
@SRTDRateTimes_Fri decimal(5,2),
@Isdefault_Fri bit,
@SRTDDSDayCode_Sat varchar(5),
@SRTDShiftType_Sat varchar(2),
@SRTDStarttime_Sat time,
@SRTDEndtime_Sat time,
@SRTDDaySplitTime_Sat time,
@SRTDMaxShEndTime_Sat time,
@SRTDMaxShHours_Sat decimal(3,1),
@SRTDOvrtimePerc_Sat decimal(4,2),
@SRTDMinMealHrs_Sat decimal(2,1),
@SRTDMealBrkMins_Sat int,
@SRTDIsRateBaseOrDayBasis_Sat bit,
@SRTDRate_Sat decimal(6,2),
@SRTDRateTimes_Sat decimal(5,2),
@Isdefault_Sat bit,
@SRTDDSDayCode_Sun varchar(5),
@SRTDShiftType_Sun varchar(2),
@SRTDStarttime_Sun time,
@SRTDEndtime_Sun time,
@SRTDDaySplitTime_Sun time,
@SRTDMaxShEndTime_Sun time,
@SRTDMaxShHours_Sun decimal(3,1),
@SRTDOvrtimePerc_Sun decimal(4,2),
@SRTDMinMealHrs_Sun decimal(2,1),
@SRTDMealBrkMins_Sun int,
@SRTDIsRateBaseOrDayBasis_Sun bit,
@SRTDRate_Sun decimal(6,2),
@SRTDRateTimes_Sun decimal(5,2),
@Isdefault_Sun bit,
@SRTDDSDayCode_Pwkdy varchar(5),
@SRTDShiftType_Pwkdy varchar(2),
@SRTDStarttime_Pwkdy time,
@SRTDEndtime_Pwkdy time,
@SRTDDaySplitTime_Pwkdy time,
@SRTDMaxShEndTime_Pwkdy time,
@SRTDMaxShHours_Pwkdy decimal(3,1),
@SRTDOvrtimePerc_Pwkdy decimal(4,2),
@SRTDMinMealHrs_Pwkdy decimal(2,1),
@SRTDMealBrkMins_Pwkdy int,
@SRTDIsRateBaseOrDayBasis_Pwkdy bit,
@SRTDRate_Pwkdy decimal(6,2),
@SRTDRateTimes_Pwkdy decimal(5,2),
@Isdefault_Pwkdy bit,
@SRTDDSDayCode_Pwkend varchar(5),
@SRTDShiftType_Pwkend varchar(2),
@SRTDStarttime_Pwkend time,
@SRTDEndtime_Pwkend time,
@SRTDDaySplitTime_Pwkend time,
@SRTDMaxShEndTime_Pwkend time,
@SRTDMaxShHours_Pwkend decimal(3,1),
@SRTDOvrtimePerc_Pwkend decimal(4,2),
@SRTDMinMealHrs_Pwkend decimal(2,1),
@SRTDMealBrkMins_Pwkend int,
@SRTDIsRateBaseOrDayBasis_Pwkend bit,
@SRTDRate_Pwkend decimal(6,2),
@SRTDRateTimes_Pwkend decimal(5,2),
@Isdefault_Pwkend bit
)
AS
BEGIN
DECLARE @return_value INT ,@Ctrl# varchar(10), @Err varchar(100),@SRTMDDId int,@SRTDMDId int
EXEC @return_value = spGetCommonSequenceCode
@TypeID =72,
@SubType1 = 'SRT',
@SubType2 = NULL,
@Userid = @User,
@Ctrl# = @Ctrl# OUTPUT
BEGIN TRY
BEGIN TRAN
SET NOCOUNT ON;
if not exists(select SRTHRTId from tblXHdrServiceRateTable where SRTHRTId=@SRTHRTId)
BEGIN
if not exists(select SRTDQualificationCode from tblXDtlServiceRateTableQualExpertise
where SRTDQualificationCode=@SRTDQualificationCode)
BEGIN
if not exists(select SRTMDTransactionId from tblXDtlServiceRateTableQualRatesMbrClt
where SRTMDTransactionId=@SRTMDTransactionId and SRTMDPayLevelId=@SRTMDPayLevelId
and SRTMDShiftTypeCode=@SRTMDShiftTypeCode and SRTMDShiftPeriodCode=@SRTMDShiftPeriodCode)
BEGIN
Insert into tblXHdrServiceRateTable(SRTHId, SRTHRTId, SRTHPRHIndOvrId, SRTHPRIndOvrTotalTax,
SRTHPRIndOvrNetTax, SRTHEffectiveDateOfStart, SRTHStatus, SRTHUser, SRTHDateTime)
values(@Ctrl# ,@SRTHRTId,@SRTHPRHIndOvrId,@SRTHPRIndOvrTotalTax,@SRTHPRIndOvrNetTax,
@SRTHEffectiveDateOfStart,@SRTHStatus,@User,getdate())
INSERT INTO tblXDtlServiceRateTableQualExpertise(SRTDHId,SRTDQualificationCode, SRTDExpertiseCode,
SRTDUser, SRTDDateTime)
Values(@Ctrl#,@SRTDQualificationCode,@SRTDExpertiseCode,@User,getdate())
set @SRTMDDId = (select SRTDId from tblXDtlServiceRateTableQualExpertise where SRTDHId=@Ctrl#
and SRTDQualificationCode=@SRTDQualificationCode)
INSERT INTO tblXDtlServiceRateTableQualRatesMbrClt(SRTMDDId, SRTMDTransactionId, SRTMDPayLevelId,
SRTMDPayLevelDesc, SRTMDShiftTypeCode, SRTMDShiftPeriodCode, SRTMDBaseRate, SRTMDWeeKDayMon,
SRTMDWeeKDayTue, SRTMDWeeKDayWed, SRTMDWeeKDayThu, SRTMDWeeKDayFri, SRTMDWeeKEndSat,
SRTMDWeekEndSun, SRTMDPubWeekDays, SRTMDPubWeekEnds, SRTMDMarkUpBaseRate, SRTMDMarkUpWeeKDayMon,
SRTMDMarkUpWeeKDayTue, SRTMDMarkUpWeeKDayWed, SRTMMarkUpWeeKDayThu, SRTMDMarkUpWeeKDayFri,
SRTMDMarkUpWeeKEndSat, SRTMDMarkUpWeekEndSun, SRTMDMarkUpPubWeekDays, SRTMDMarkUpPubWeekEnds,
SRTMDClientBaseRate, SRTMDClientWeeKDayMon, SRTMDClientWeeKDayTue, SRTMDClientWeeKDayWed,
SRTMDClientWeeKDayThu, SRTMDClientWeeKDayFri, SRTMDClientWeeKEndSat, SRTMDClientWeekEndSun,
SRTMDClientPubWeekDays, SRTMDClientPubWeekEnds, SRTMDUser, SRTMDDateTime)
Values(@SRTMDDId,@SRTMDTransactionId, @SRTMDPayLevelId,
@SRTMDPayLevelDesc, @SRTMDShiftTypeCode, @SRTMDShiftPeriodCode, @SRTMDBaseRate, @SRTMDWeeKDayMon,
@SRTMDWeeKDayTue, @SRTMDWeeKDayWed, @SRTMDWeeKDayThu, @SRTMDWeeKDayFri, @SRTMDWeeKEndSat,
@SRTMDWeekEndSun, @SRTMDPubWeekDays, @SRTMDPubWeekEnds, @SRTMDMarkUpBaseRate, @SRTMDMarkUpWeeKDayMon,
@SRTMDMarkUpWeeKDayTue, @SRTMDMarkUpWeeKDayWed, @SRTMMarkUpWeeKDayThu, @SRTMDMarkUpWeeKDayFri,
@SRTMDMarkUpWeeKEndSat, @SRTMDMarkUpWeekEndSun, @SRTMDMarkUpPubWeekDays, @SRTMDMarkUpPubWeekEnds,
@SRTMDClientBaseRate, @SRTMDClientWeeKDayMon, @SRTMDClientWeeKDayTue, @SRTMDClientWeeKDayWed,
@SRTMDClientWeeKDayThu, @SRTMDClientWeeKDayFri, @SRTMDClientWeeKEndSat, @SRTMDClientWeekEndSun,
@SRTMDClientPubWeekDays, @SRTMDClientPubWeekEnds,@User,getdate())
set @SRTDMDId = (select SRTMDId from dbo.tblXDtlServiceRateTableQualRatesMbrClt where SRTMDDId=@SRTMDDId
and SRTMDTransactionId=@SRTMDTransactionId and SRTMDPayLevelId=@SRTMDPayLevelId
and SRTMDShiftTypeCode=@SRTMDShiftTypeCode and SRTMDShiftPeriodCode=@SRTMDShiftPeriodCode)
if (@Isdefault_Mon=1)
BEGIN
INSERT INTO tblXDtlServiceRateTableDays (SRTDMDId, SRTDDSDayCode, SRTDShiftType, SRTDStarttime,
SRTDEndtime, SRTDDaySplitTime, SRTDMaxShEndTime, SRTDMaxShHours, SRTDOvrtimePerc, SRTDMinMealHrs,
SRTDMealBrkMins, SRTDIsRateBaseOrDayBasis, SRTDRate, SRTDRateTimes, SRTDUser, SRTDDateTime)
Values(@SRTDMDId,@SRTDDSDayCode_Mon,@SRTDShiftType_Mon,@SRTDStarttime_Mon,@SRTDEndtime_Mon,
@SRTDDaySplitTime_Mon,@SRTDMaxShEndTime_Mon,@SRTDMaxShHours_Mon,@SRTDOvrtimePerc_Mon,
@SRTDMinMealHrs_Mon,@SRTDMealBrkMins_Mon,@SRTDIsRateBaseOrDayBasis_Mon,@SRTDRate_Mon,
@SRTDRateTimes_Mon,@User,getdate())
END
if (@Isdefault_Tue=1)
BEGIN
INSERT INTO tblXDtlServiceRateTableDays (SRTDMDId, SRTDDSDayCode, SRTDShiftType, SRTDStarttime,
SRTDEndtime, SRTDDaySplitTime, SRTDMaxShEndTime, SRTDMaxShHours, SRTDOvrtimePerc, SRTDMinMealHrs,
SRTDMealBrkMins, SRTDIsRateBaseOrDayBasis, SRTDRate, SRTDRateTimes, SRTDUser, SRTDDateTime)
Values(@SRTDMDId,@SRTDDSDayCode_Tue,@SRTDShiftType_Tue,@SRTDStarttime_Tue,@SRTDEndtime_Tue,
@SRTDDaySplitTime_Tue,@SRTDMaxShEndTime_Tue,@SRTDMaxShHours_Tue,@SRTDOvrtimePerc_Tue,
@SRTDMinMealHrs_Tue,@SRTDMealBrkMins_Tue,@SRTDIsRateBaseOrDayBasis_Tue,@SRTDRate_Tue,
@SRTDRateTimes_Tue,@User,getdate())
END
if (@Isdefault_Wed=1)
BEGIN
INSERT INTO tblXDtlServiceRateTableDays (SRTDMDId, SRTDDSDayCode, SRTDShiftType, SRTDStarttime,
SRTDEndtime, SRTDDaySplitTime, SRTDMaxShEndTime, SRTDMaxShHours, SRTDOvrtimePerc, SRTDMinMealHrs,
SRTDMealBrkMins, SRTDIsRateBaseOrDayBasis, SRTDRate, SRTDRateTimes, SRTDUser, SRTDDateTime)
Values(@SRTDMDId,@SRTDDSDayCode_Wed,@SRTDShiftType_Wed,@SRTDStarttime_Wed,@SRTDEndtime_Wed,
@SRTDDaySplitTime_Wed,@SRTDMaxShEndTime_Wed,@SRTDMaxShHours_Wed,@SRTDOvrtimePerc_Wed,
@SRTDMinMealHrs_Wed,@SRTDMealBrkMins_Wed,@SRTDIsRateBaseOrDayBasis_Wed,@SRTDRate_Wed,
@SRTDRateTimes_Wed,@User,getdate())
END
if (@Isdefault_Thu=1)
BEGIN
INSERT INTO tblXDtlServiceRateTableDays (SRTDMDId, SRTDDSDayCode, SRTDShiftType, SRTDStarttime,
SRTDEndtime, SRTDDaySplitTime, SRTDMaxShEndTime, SRTDMaxShHours, SRTDOvrtimePerc, SRTDMinMealHrs,
SRTDMealBrkMins, SRTDIsRateBaseOrDayBasis, SRTDRate, SRTDRateTimes, SRTDUser, SRTDDateTime)
Values(@SRTDMDId,@SRTDDSDayCode_Thu,@SRTDShiftType_Thu,@SRTDStarttime_Thu,@SRTDEndtime_Thu,
@SRTDDaySplitTime_Thu,@SRTDMaxShEndTime_Thu,@SRTDMaxShHours_Thu,@SRTDOvrtimePerc_Thu,
@SRTDMinMealHrs_Thu,@SRTDMealBrkMins_Thu,@SRTDIsRateBaseOrDayBasis_Thu,@SRTDRate_Thu,
@SRTDRateTimes_Thu,@User,getdate())
END
if (@Isdefault_Fri=1)
BEGIN
INSERT INTO tblXDtlServiceRateTableDays (SRTDMDId, SRTDDSDayCode, SRTDShiftType, SRTDStarttime,
SRTDEndtime, SRTDDaySplitTime, SRTDMaxShEndTime, SRTDMaxShHours, SRTDOvrtimePerc, SRTDMinMealHrs,
SRTDMealBrkMins, SRTDIsRateBaseOrDayBasis, SRTDRate, SRTDRateTimes, SRTDUser, SRTDDateTime)
Values(@SRTDMDId,@SRTDDSDayCode_Fri,@SRTDShiftType_Fri,@SRTDStarttime_Fri,@SRTDEndtime_Fri,
@SRTDDaySplitTime_Fri,@SRTDMaxShEndTime_Fri,@SRTDMaxShHours_Fri,@SRTDOvrtimePerc_Fri,
@SRTDMinMealHrs_Fri,@SRTDMealBrkMins_Fri,@SRTDIsRateBaseOrDayBasis_Fri,@SRTDRate_Fri,
@SRTDRateTimes_Fri,@User,getdate())
END
if (@Isdefault_Sat=1)
BEGIN
INSERT INTO tblXDtlServiceRateTableDays (SRTDMDId, SRTDDSDayCode, SRTDShiftType, SRTDStarttime,
SRTDEndtime, SRTDDaySplitTime, SRTDMaxShEndTime, SRTDMaxShHours, SRTDOvrtimePerc, SRTDMinMealHrs,
SRTDMealBrkMins, SRTDIsRateBaseOrDayBasis, SRTDRate, SRTDRateTimes, SRTDUser, SRTDDateTime)
Values(@SRTDMDId,@SRTDDSDayCode_Sat,@SRTDShiftType_Sat,@SRTDStarttime_Sat,@SRTDEndtime_Sat,
@SRTDDaySplitTime_Sat,@SRTDMaxShEndTime_Sat,@SRTDMaxShHours_Sat,@SRTDOvrtimePerc_Sat,
@SRTDMinMealHrs_Sat,@SRTDMealBrkMins_Sat,@SRTDIsRateBaseOrDayBasis_Sat,@SRTDRate_Sat,
@SRTDRateTimes_Sat,@User,getdate())
END
if (@Isdefault_Sun=1)
BEGIN
INSERT INTO tblXDtlServiceRateTableDays (SRTDMDId, SRTDDSDayCode, SRTDShiftType, SRTDStarttime,
SRTDEndtime, SRTDDaySplitTime, SRTDMaxShEndTime, SRTDMaxShHours, SRTDOvrtimePerc, SRTDMinMealHrs,
SRTDMealBrkMins, SRTDIsRateBaseOrDayBasis, SRTDRate, SRTDRateTimes, SRTDUser, SRTDDateTime)
Values(@SRTDMDId,@SRTDDSDayCode_Sun,@SRTDShiftType_Sun,@SRTDStarttime_Sun,@SRTDEndtime_Sun,
@SRTDDaySplitTime_Sun,@SRTDMaxShEndTime_Sun,@SRTDMaxShHours_Sun,@SRTDOvrtimePerc_Sun,
@SRTDMinMealHrs_Sun,@SRTDMealBrkMins_Sun,@SRTDIsRateBaseOrDayBasis_Sun,@SRTDRate_Sun,
@SRTDRateTimes_Sun,@User,getdate())
END
if (@Isdefault_Pwkdy=1)
BEGIN
INSERT INTO tblXDtlServiceRateTableDays (SRTDMDId, SRTDDSDayCode, SRTDShiftType, SRTDStarttime,
SRTDEndtime, SRTDDaySplitTime, SRTDMaxShEndTime, SRTDMaxShHours, SRTDOvrtimePerc, SRTDMinMealHrs,
SRTDMealBrkMins, SRTDIsRateBaseOrDayBasis, SRTDRate, SRTDRateTimes, SRTDUser, SRTDDateTime)
Values(@SRTDMDId,@SRTDDSDayCode_Pwkdy,@SRTDShiftType_Pwkdy,@SRTDStarttime_Pwkdy,@SRTDEndtime_Pwkdy,
@SRTDDaySplitTime_Pwkdy,@SRTDMaxShEndTime_Pwkdy,@SRTDMaxShHours_Pwkdy,@SRTDOvrtimePerc_Pwkdy,
@SRTDMinMealHrs_Pwkdy,@SRTDMealBrkMins_Pwkdy,@SRTDIsRateBaseOrDayBasis_Pwkdy,@SRTDRate_Pwkdy,
@SRTDRateTimes_Pwkdy,@User,getdate())
END
if (@Isdefault_Pwkend=1)
BEGIN
INSERT INTO tblXDtlServiceRateTableDays (SRTDMDId, SRTDDSDayCode, SRTDShiftType, SRTDStarttime,
SRTDEndtime, SRTDDaySplitTime, SRTDMaxShEndTime, SRTDMaxShHours, SRTDOvrtimePerc, SRTDMinMealHrs,
SRTDMealBrkMins, SRTDIsRateBaseOrDayBasis, SRTDRate, SRTDRateTimes, SRTDUser, SRTDDateTime)
Values(@SRTDMDId,@SRTDDSDayCode_Pwkend,@SRTDShiftType_Pwkend,@SRTDStarttime_Pwkend,@SRTDEndtime_Pwkend,
@SRTDDaySplitTime_Pwkend,@SRTDMaxShEndTime_Pwkend,@SRTDMaxShHours_Pwkend,@SRTDOvrtimePerc_Pwkend,
@SRTDMinMealHrs_Pwkend,@SRTDMealBrkMins_Pwkend,@SRTDIsRateBaseOrDayBasis_Pwkend,@SRTDRate_Pwkend,
@SRTDRateTimes_Pwkend,@User,getdate())
END
COMMIT TRAN
RETURN 0
END
END
END
END TRY
BEGIN CATCH
ROLLBACK TRAN;
DECLARE @ErrorMessage NVARCHAR(4000),
@ErrorSeverity INT,@ErrorState INT;
SET @ErrorMessage ='-1'+ERROR_MESSAGE();
SET @ErrorSeverity = ERROR_SEVERITY();
SET @ErrorState = ERROR_STATE();
RAISERROR(@ErrorMessage,@ErrorSeverity,@ErrorState);
RETURN 1
END CATCH
END