stored procedure type conversion error

Question

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 15-Oct-12 19:40pm

Member 8172875

Updated 15-Oct-12 20:08pm

v3

Add a Solution

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

3 solutions

Solution 1

Please provide table structure of UCMS_PTECH_MED_SALARY.
so we can find where the exact error comes.

Posted 15-Oct-12 20:02pm

Bhushan Shah1988

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

MT_ · Accepted Answer · 2012-10-15T20:08:00

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

Posted 15-Oct-12 20:08pm

MT_

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

MT_ 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

**AmitGajjar** · Accepted Answer · 2012-10-15T20:09:00

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.

Posted 15-Oct-12 20:09pm

AmitGajjar

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.