Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-Server CURSOR , +
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())
--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.
 
"
 
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
Posted 15-Oct-12 20:40pm
Edited 15-Oct-12 21:08pm
v3
Comments
@AmitGajjar at 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 at 16-Oct-12 2:02am
   
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
@AmitGajjar at 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 at 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: bad
good
Please Sign up or sign in to vote.

Solution 1

Please provide table structure of UCMS_PTECH_MED_SALARY.
so we can find where the exact error comes.
  Permalink  
Comments
snehasish nandy at 16-Oct-12 2:06am
   
ask in comment section not in solution part...
Member 8172875 at 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: bad
good
Please Sign up or sign in to vote.

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
  Permalink  
Comments
Member 8172875 at 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 at 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 at 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 at 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 at 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 at 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 at 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: bad
good
Please Sign up or sign in to vote.

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.
  Permalink  
Comments
Member 8172875 at 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 at 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 at 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 at 16-Oct-12 7:47am
   
You can create separate table to insert values.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Advertise | Privacy | Mobile
Web01 | 2.8.1411022.1 | Last Updated 16 Oct 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100