存储过程 - 将数据类型varchar转换为数字时出错

时间:2022-08-18 16:36:51

When running a simple stored procedure, I'm getting this error and frankly I don't know why.

运行简单的存储过程时,我收到此错误,坦率地说,我不知道为什么。

Msg 8114, Level 16, State 5, Procedure PROC_TEST, Line 15
Error converting data type varchar to numeric.

消息8114,级别16,状态5,过程PROC_TEST,行15错误将数据类型varchar转换为数字。

Columns:

  • SI_ID is NUMERIC(19,0)
  • SI_ID是NUMERIC(19,0)

  • SI_COMPANYID is NUMERIC(19,0)
  • SI_COMPANYID是NUMERIC(19,0)

  • SI_LOGINID is VARCHAR(100)
  • SI_LOGINID是VARCHAR(100)

Any idea why?

知道为什么吗?

This is the stored procedure:

这是存储过程:

CREATE PROCEDURE [xxxxxx].[PROC_TEST] 
AS
BEGIN    
    SET NOCOUNT ON

    DECLARE
          @id NUMERIC(19,0),
          @login VARCHAR(100);

    DECLARE user_cursor CURSOR       <<----- Line 15
    FOR
       SELECT 
           SI_ID, SI_LOGINID 
       FROM SI_USER 
       WHERE SI_COMPANYID = 123

    OPEN user_cursor

    FETCH NEXT FROM user_cursor into @id, @login

    WHILE @@FETCH_STATUS = 0 
    BEGIN
          PRINT @id + ', ' + @login

          FETCH NEXT FROM user_cursor into @id, @login
    END

    CLOSE user_cursor
    DEALLOCATE user_cursor
END

1 个解决方案

#1


SQL error line reference is inaccurate. The error seems to be this line: PRINT @id + ', ' + @login

SQL错误行引用不准确。错误似乎是这一行:PRINT @id +','+ @ login

Try PRINT CONVERT(varchar, @id) + ', ' + @login

尝试PRINT CONVERT(varchar,@ id)+','+ @ login

#1


SQL error line reference is inaccurate. The error seems to be this line: PRINT @id + ', ' + @login

SQL错误行引用不准确。错误似乎是这一行:PRINT @id +','+ @ login

Try PRINT CONVERT(varchar, @id) + ', ' + @login

尝试PRINT CONVERT(varchar,@ id)+','+ @ login