使用存储过程中的游标获取错误将数据复制到临时表? [英] Copy the Data to Temporary table using cursor in stored procedure getting error?
问题描述
来源:
创建程序Balu_Cursor
AS
BEGIN
DECLARE @EmptypeID INT
DECLARE @Name VARCHAR(50)
DECLARE @Salary INT
DECLARE @TempTable table(
EmpID INT,
Empname varchar,
工资数字
)
DECLARE cur_print CURSOR FOR
SELECT Empno,Empname,Salary
FROM tblEmployee OPEN cur_print
FETCH NEXT FROM cur_print INTO @ EmptypeID,@ Name, @Salary
WHILE @@ FETCH_STATUS = 0
BEGIN
插入@TempTable(EmpID,Empname,薪水)值(@ EmptypeID,@ Name,@ Salary)
PRINT @EmptypeID PRINT @Name PRINT @Salary
选择@EmptypeID作为EmpNo,@ Name Name,@ Salary来自tblEmployee的工资,其中Empno = @ EmptypeID
FETCH NEXT FROM cur_print INTO @ EmptypeID,@ Name,@ Salary
END
CLOSE cur_print
DEALLOCATE cur_print
结束
错误:
Msg 8152,Level 16,State 14,Line 18
字符串或二进制数据将被截断。
语句已终止。
source:
CREATE PROCEDURE Balu_Cursor
AS
BEGIN
DECLARE @EmptypeID INT
DECLARE @Name VARCHAR(50)
DECLARE @Salary INT
DECLARE @TempTable table(
EmpID INT,
Empname varchar,
Salary numeric
)
DECLARE cur_print CURSOR FOR
SELECT Empno,Empname,Salary
FROM tblEmployee OPEN cur_print
FETCH NEXT FROM cur_print INTO @EmptypeID,@Name,@Salary
WHILE @@FETCH_STATUS = 0
BEGIN
insert into @TempTable(EmpID,Empname,Salary)values(@EmptypeID,@Name,@Salary)
PRINT @EmptypeID PRINT @Name PRINT @Salary
Select @EmptypeID as EmpNo,@Name Name,@Salary Salary from tblEmployee where Empno=@EmptypeID
FETCH NEXT FROM cur_print INTO @EmptypeID,@Name,@Salary
END
CLOSE cur_print
DEALLOCATE cur_print
END
ERROR:
Msg 8152, Level 16, State 14, Line 18
String or binary data would be truncated.
The statement has been terminated.
推荐答案
您的临时表和参数需要具有相同的数据类型。
所以:
DECLARE @TempTable表(
EmpID INT,
Empname varchar(50),
薪资INT
)
Your temp table and parameters need to have the same data types.
So:
DECLARE @TempTable table(
EmpID INT,
Empname varchar(50),
Salary INT
)
这篇关于使用存储过程中的游标获取错误将数据复制到临时表?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!