存储过程类型转换错误 [英] stored procedure type conversion error
问题描述
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
我收到以下提到的错误:
消息245，级别16，状态1，过程abcde，第84行
转换nvarchar值违反主键约束" PK_UCMS_PTECH_MED_SALARY_1"时，转换失败.无法在对象"dbo.UCMS_PTECH_MED_SALARY"中插入重复键，以将数据类型设置为int.
"
请帮助我解决此问题
数据库结构
priority_no主键int
DA货币Basic_pay货币
Transp_Alwnc货币
Pay_Band货币
_Payment_Pay货币
Sal_Date主键日期时间与getdate
(我想为表中存在的所有行插入重复的行，其中DA和Transp_alwnc的值递增7％.<
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
推荐答案
请提供 UCMS_PTECH_MED_SALARY .
这样我们就可以找到确切的错误出处.
Please provide table structure of UCMS_PTECH_MED_SALARY.
so we can find where the exact error comes.
您正在尝试重新插入具有相同priority_no的记录.
如果您需要新记录并更新所有其他值，请不要将priority_no标记为PK.您可以添加新的列ID并将其单独标记(或与priority_no一起标记为PK).
否则，如果您只需要添加除"Priority_NO"之外的数据，则写入更新而不是插入.
希望能有所帮助.如果是这样，请将其标记为答案/赞.
谢谢，
Milind
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
我相信您正在游标内进行一些计算，然后需要为该记录更新DA和Transp_Alwnc.
这是您的存储过程的一些问题，
您不需要插入新记录，而是需要使用更新查询来更新该特定行的DA和Trans_Alwnc值.
所以错误是当您选择特定的行并使用更新的DA和Trans_Alwnc插入该行时，它会给您带来异常，因为priority_no将被插入多次，并且它是主键(因此在整个表中应该是唯一的).
希望这些信息足以理解.
谢谢.
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.
这篇关于存储过程类型转换错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
