set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
alter
Procedure [dbo].[abcde]
(
@pErrFlag
Char(1) Output,
@pErrDesc
VARCHAR(500) Output
)
AS
Begin
Begin
Try
DECLARE
EMPCUR CURSOR FOR
SELECT
Priority_No, Pay_Band, Grade_Pay, Basic_Pay, DA, Transp_Alwnc
FROM
dbo.UCMS_PTECH_MED_SALARY
where
Sal_Date IN(Select Max(Sal_Date) from dbo.UCMS_PTECH_MED_SALARY group by Priority_No)
DECLARE
@Priority_No int, @Basic_Pay Money, @DA Money, @Pay_Band Money,
@Grade_Pay Money, @Transp_Alwnc Money
OPEN
EMPCUR
FETCH
NEXT FROM EMPCUR INTO @Priority_No, @Pay_Band, @Grade_Pay, @Basic_Pay, @DA, @Transp_Alwnc
WHILE
@@FETCH_STATUS=0
BEGIN
set
@DA = (@DA *(7/100))
set
@Transp_Alwnc = (@Transp_Alwnc*(7/100))
Insert into dbo.UCMS_PTECH_MED_SALARY(Priority_No, DA, Transp_Alwnc, Sal_Date)
Values(@Priority_No, @DA, @Transp_Alwnc, getdate())
FETCH
NEXT FROM EMPCUR INTO @Priority_No, @Pay_Band, @Grade_Pay, @Basic_Pay, @DA, @Transp_Alwnc
END
CLOSE
EMPCUR
DEALLOCATE
EMPCUR
Set
@pErrFlag = 'S'
Set
@pErrDesc = 'S'
End
Try
Begin
Catch
Set
@pErrDesc = ERROR_MESSAGE() + '' + ERROR_LINE() + '' + ERROR_PROCEDURE()
Set
@pErrFlag = 'E'
If
@@trancount > 0
Rollback
RAISERROR
(@pErrDesc,11,1);
End
Catch
End
i am getting error mentioned below:
"Msg 245, Level 16, State 1, Procedure abcde, Line 84
Conversion failed when converting the nvarchar value 'Violation of PRIMARY KEY constraint 'PK_UCMS_PTECH_MED_SALARY_1'. Cannot insert duplicate key in object 'dbo.UCMS_PTECH_MED_SALARY'.' to data type int.
"
Please help me to resolve this problem
structure of database
priority_no primary key int
DA money Basic_pay money
Transp_Alwnc money
Pay_Band money
Grade_Pay money
Sal_Date primary key datetime with getdate
( i want to insert a duplicate row for all rows present in table with 7% incremented value of DA and Transp_alwnc