13,000,324 members (74,953 online)
Rate this:
See more: , +
```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,

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())
--select @Priority_No, Emp_Name, @DA, @Transp_Alwnc)

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.

"

structure of database
priority_no primary key int
DA money Basic_pay money
Transp_Alwnc money
Pay_Band 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
Posted 15-Oct-12 19:40pm
Updated 15-Oct-12 20:08pm
v3
@AmitGajjar 16-Oct-12 1:49am

Please post database structure of UCMS_PTECH_MED_SALARY table. i think you need to set autoincrement property for primary key column if you are not specify it in insert statement.
Member 8172875 16-Oct-12 2:02am

structure of database

priority_no primary key int
DA money
Basic_pay money
Transp_Alwnc money
Pay_Band 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
@AmitGajjar 16-Oct-12 2:11am

you can not insert duplicate rows for given primary key. you need to create other table or make PK to nonPK.
Member 8172875 16-Oct-12 2:25am

hi amit, thanks for the solution.. yes i want to do the same thing... but i want all the previous record..if m using update command then it will update that row and lost previous data... for checking purpose i modify my table structure and set sal_date primary key instead of priority_no+sal_date. it works but i am getting two problem 1. i have 2 rows after insertion total no of rows will be 4...but there are more than 3000 rows get inserted in my table 2. the value of DA and Transp will be 0.000

Rate this:

## Solution 1

Please provide table structure of UCMS_PTECH_MED_SALARY.
so we can find where the exact error comes.
snehasish nandy 16-Oct-12 2:06am

ask in comment section not in solution part...
Member 8172875 16-Oct-12 2:07am

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
Rate this:

## Solution 2

Hi,
You are trying to reinsert record with same priority_no.

If you need a new record with all other values updated, don't mark priority_no as PK. You can add a new column ID and mark it alone (or along with priority_no as composite) PK.
Else if you just need to udpate data other than "Priority_NO", write update instead of insert.

Hope that helps. If it does, mark it as answer/upvote.

Thanks,
Milind
Member 8172875 16-Oct-12 2:12am

hi milind .. thanks for the solution .. but i cant update bcz if i update that row then i cant get previous data.. i need all the updated as well as previous data.

and other thing is i have composite primary key (priority_no and sal_date(getdate())) still i am not able to insert
Milind Thakkar 16-Oct-12 2:17am

That is because sal_date is same date. If you are using date and time there then you can insert I think - Milind
Member 8172875 16-Oct-12 2:20am

but in sal_date is current date. my previous date is 21/08/2012 and current date is 16/10/2012. both the date are different with same priority number. so i dont think so there is a problem in primary composite key
Milind Thakkar 16-Oct-12 2:40am

Aah...I believe, since you have max(sal_date) in where clause of a cursor, it is re-bringing the just-updated raw and then both priority number date is same if you try to re-update the same raw on same date !!
I think you need to re-look at the cursor.
Member 8172875 16-Oct-12 2:47am

but when i remove priority_no as primary key for checking purpose. it will insert rows. but with some problem:

1. i have 2 rows after insertion total no of rows will be 4...but there are more than 3000 rows get inserted in my table

2. the value of DA and Transp will be 0.000. not updated by its calculated value
Milind Thakkar 16-Oct-12 2:53am

Thats supports my point of cursor re-fetching just updated row hence so many insertion. Rather than cursor get the required rows in a temporary table then do the process (of calculation and insertion). That should definitely solve ur problem.
If it does, mark the answer as solution and/or upvote.
Milind
Member 8172875 16-Oct-12 4:15am

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER
Procedure [dbo].[cursorPtechMedSal]

(

@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, Gross_Pay, Nett_Pay

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,
@temp money, @temp1 money,
@Grade_Pay Money, @Transp_Alwnc Money, @Gross_Pay money, @Nett_Pay money

OPEN
EMPCUR

FETCH
NEXT FROM EMPCUR INTO @Priority_No, @Pay_Band, @Grade_Pay, @Basic_Pay, @DA, @Transp_Alwnc, @Gross_Pay, @Nett_Pay

WHILE
@@FETCH_STATUS=0

BEGIN

set
@temp =@DA

set
@temp1 = @Transp_Alwnc

set
@DA = @DA+ @DA * 0.07
set
@temp=@DA-@temp

set
@Transp_Alwnc = @Transp_Alwnc + @Transp_Alwnc * 0.07

set
@temp1=@Transp_Alwnc-@temp1

set
@Gross_Pay = @Gross_Pay + @temp + @temp1
set
@Nett_Pay = @Nett_Pay + @temp + @temp1

insert dbo.Backup_UCMS_PTECH_MED_SALARY (Priority_NO, DA, TA)
Values(@Priority_No, @DA, @Transp_Alwnc)

update dbo.UCMS_PTECH_MED_SALARY
set DA=@DA, Transp_Alwnc= @Transp_Alwnc , Gross_Pay=@Gross_Pay, Nett_Pay=@Nett_Pay

FETCH
NEXT FROM EMPCUR INTO @Priority_No, @Pay_Band, @Grade_Pay, @Basic_Pay, @DA, @Transp_Alwnc, @Gross_Pay, @Nett_Pay

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 did not get the correct value when i update DA and Transp_Alwnc.. update command update the result twice and save it.it means but insert command work properly.
for e.g. if i have DA 1000 in row1 and row2.but i m getting result 1144.900 for both the rows. but in insert command result is row1=1070 and row2=1144.900 that is correct result but update command gives wrong values. it update both the row with last incremented value

Rate this:

## Solution 3

Hi,

What i believe is you are doing some calculation inside the cursor and then you need to update DA and Transp_Alwnc for that record.

Here are some issues with your stored procedure,

you do not need to insert new record instead you need to use update query to update DA and Trans_Alwnc value for that particular row.

So error is when you are selecting particular row and insert the same with updated DA and Trans_Alwnc, it will give you exception as priority_no will be inserted multiple times and it is primary key(so it should be unique through out the table.)

hope this information is enough to understand.

Thanks.
Member 8172875 16-Oct-12 2:24am

hi amit, thanks for the solution..

yes i want to do the same thing... but i want all the previous record..if m using update command then it will update that row and lost previous data...

for checking purpose i modify my table structure and set sal_date primary key instead of priority_no+sal_date.

it works but i am getting two problem
1. i have 2 rows after insertion total no of rows will be 4...but there are more than 3000 rows get inserted in my table

2. the value of DA and Transp will be 0.000
@AmitGajjar 16-Oct-12 2:27am

For checking purpose you can move those records in history table. and add some link in your page to see the history.
Member 8172875 16-Oct-12 4:38am

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER
Procedure [dbo].[cursorPtechMed]

(

@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, Gross_Pay, Nett_Pay

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,
@temp money, @temp1 money,
@Grade_Pay Money, @Transp_Alwnc Money, @Gross_Pay money, @Nett_Pay money

OPEN
EMPCUR

FETCH
NEXT FROM EMPCUR INTO @Priority_No, @Pay_Band, @Grade_Pay, @Basic_Pay, @DA, @Transp_Alwnc, @Gross_Pay, @Nett_Pay

WHILE
@@FETCH_STATUS=0

BEGIN

set
@temp =@DA

set
@temp1 = @Transp_Alwnc

set
@DA = @DA+ @DA * 0.07
set
@temp=@DA-@temp

set
@Transp_Alwnc = @Transp_Alwnc + @Transp_Alwnc * 0.07

set
@temp1=@Transp_Alwnc-@temp1

set
@Gross_Pay = @Gross_Pay + @temp + @temp1
set
@Nett_Pay = @Nett_Pay + @temp + @temp1

update dbo.UCMS_PTECH_MED_SALARY
set DA=@DA, Transp_Alwnc= @Transp_Alwnc , Gross_Pay=@Gross_Pay, Nett_Pay=@Nett_Pay

FETCH
NEXT FROM EMPCUR INTO @Priority_No, @Pay_Band, @Grade_Pay, @Basic_Pay, @DA, @Transp_Alwnc, @Gross_Pay, @Nett_Pay

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 did not get the correct value when i update DA and Transp_Alwnc.. update command update the result twice and save it.it means but insert command work properly. for e.g. if i have DA 1000 in row1 and row2.but i m getting result 1144.900 for both the rows. but in insert command result is row1=1070 and row2=1144.900 that is correct result but update command gives wrong values. it update both the row with last incremented value

@AmitGajjar 16-Oct-12 7:47am

You can create separate table to insert values.

Top Experts
Last 24hrsThis month
 RickZeeland 104 OriginalGriff 60 Michael_Davies 50 ppolymorphe 45 Richard Deeming 40
 OriginalGriff 4,607 ppolymorphe 2,317 RickZeeland 2,211 Kornfeld Eliyahu Peter 1,993 Richard Deeming 1,633