CODEWITHSUNDEEP
sqlsql-serverstored-proceduressql-server-2008-r2
TheEwook
TheEwook

Reputation: 11117

Stored procedure - Error converting data type varchar to numeric

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.

Columns:

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

Upvotes: 2

Views: 6221

Answers (1)

Eric
Eric

Reputation: 5743

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

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

Upvotes: 9

Related Questions