Stored Procedure Data Type Conversion Error

I try to execute the following SP it gives me this following error.

Msg 8114, Level 16, State 5, Procedure TEST, Line 55 [Batch Start Line 12] Error converting data type nvarchar to bigint.

I tries casting the value when passing insert into values but still gave the same error.

Tables contain doc_id BIGINT & line_id INT

When I set @doc_id VARCHAR(100)  = NULL and @line_id  VARCHAR(100) = NULL

Still it prints

Msg 8114, Level 16, State 5, Procedure TEST, Line 55 [Batch Start Line 12] Error converting data type nvarchar to bigint.

Following is the procedure I created.

ALTER PROCEDURE [dbo].[TEST] 

    @companycode VARCHAR(100)  = NULL,
    @division VARCHAR(100)  = NULL,
    @doc_id BIGINT  = NULL,
    --@doc_id VARCHAR(100)  = NULL,
    @doc_type_id VARCHAR(100)  = NULL,  
    @fileid VARCHAR(100)  = NULL,   
    @line_id INT  = NULL,
    --@line_id  VARCHAR(100) = NULL,
    @m3UpdateStatus VARCHAR(100)  = NULL,
    @m3VoucherNo VARCHAR(100)  = NULL,
    @posted_by VARCHAR(100)  = NULL,
    @post_priority VARCHAR(100)  = NULL,
    @samsung_doc_no VARCHAR(100)  = NULL,
    @serp_doctype VARCHAR(100)  = NULL,
    @serp_status_id VARCHAR(100)  = NULL,
    @year VARCHAR(100)  = NULL,
    @period VARCHAR(100)  = NULL,
    @transactionType VARCHAR(100)  = NULL

    AS
    BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    print 1
    DECLARE db_cursor CURSOR FOR 
    SELECT [doc_id] --@doc_id
          ,[samsung_doc_no] --@samsung_doc_no
          ,[posted_by] --@posted_by
          ,[post_priority] --@post_priority
          ,[serp_status_id] --@serp_status_id
          ,[line_id] --@line_id
          ,[fileid] --@fileid
          ,[serp_doctype] --@serp_doctype
          ,[doc_type_id] --@doc_type_id
          ,[companycode] --@companycode
          ,[m3VoucherNo] --@m3VoucherNo
          ,[m3UpdateStatus] --@m3UpdateStatus
          ,[division] --@division
          ,[year] --@year
          ,[period] --@period
          ,[transactionType] --@transactionType
      FROM [dbo].[post]
      LEFT JOIN [dbo].[DM] ON [dbo].[DM].[BA] = [dbo].[post].[serp_doctype]
      WHERE [dbo].[DM].[DT] = '2' AND [dbo].[post].[serp_status_id] = 'TEST'
       AND  [dbo].[post].[serp_post_id] IN (4,3,2,1,0)
    print 2

    Declare @now datetime = getdate();

    OPEN db_cursor  
    print 3
    FETCH NEXT FROM db_cursor INTO @companycode,@division,@doc_id,@doc_type_id,@fileid,@line_id,@m3UpdateStatus,@m3VoucherNo,@posted_by,@post_priority,@samsung_doc_no,@serp_doctype,@serp_status_id,@year,@period,@transactionType
    print 4
    WHILE @@FETCH_STATUS = 0  
    BEGIN  
    print 5
          INSERT INTO [dbo].[post] (doc_id ,samsung_doc_no ,created_date ,process_type,posted_by,post_priority,posting_date,status_id,serp_status_id,line_id,fileid,serp_doctype,doc_type_id,companycode,m3VoucherNo,m3UpdateStatus,division,year,period,transactionType) 
          VALUES(@doc_id,@samsung_doc_no,@now,'C',@posted_by,@post_priority,@now,'L',@serp_status_id,@line_id,@fileid,@serp_doctype,@doc_type_id,@companycode,@m3VoucherNo,@m3UpdateStatus,@division,@year,@period,@transactionType)

          FETCH NEXT FROM db_cursor INTO @companycode,@division,@doc_id,@doc_type_id,@fileid,@line_id,@m3UpdateStatus,@m3VoucherNo,@posted_by,@post_priority,@samsung_doc_no,@serp_doctype,@serp_status_id,@year,@period,@transactionType
    END 
    print 6
    CLOSE db_cursor  
    DEALLOCATE db_cursor

   END

Answer

The variable order in FETCH is incorrect. [doc_id] is being saved in @companycode declared as VARCHAR(100). You should check the other variable order as there are not correctly specified.

FETCH NEXT FROM db_cursor INTO @doc_id,@samsung_doc_no,@posted_by,@post_priority,@serp_status_id,@line_id,@fileid,@serp_doctype,@doc_type_id,@companycode,@m3VoucherNo,@m3UpdateStatus,@division,@year,@period,@transactionType

Advertisement

Answer