Click here to Skip to main content
15,883,705 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more: , +
SQL
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
Updated 15-Oct-12 20:08pm
v3
Comments
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
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 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

Please provide table structure of UCMS_PTECH_MED_SALARY.
so we can find where the exact error comes.
 
Share this answer
 
Comments
[no name] 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
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
 
Share this answer
 
Comments
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
MT_ 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
MT_ 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
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.
 
Share this answer
 
Comments
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.

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900