存储过程数据类型转换错误

Question

我尝试执行以下SP，它会给出以下错误。

Msg 8114，级别16，状态5，过程测试，第55行批处理开始行12错误，将数据类型nvarchar转换为bigint。

当将insert传递到值时，我尝试转换值，但仍然给出了相同的错误。

表包含doc_id BIGINT和line_id INT

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

但还是印出来了

Msg 8114，级别16，状态5，过程测试，第55行批处理开始行12错误，将数据类型nvarchar转换为bigint。

下面是我创建的过程。

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 1

FETCH中的变量顺序不正确。[doc_id]保存在@companycode中，声明为VARCHAR(100)。如果未正确指定，则应检查其他变量顺序。

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