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
Advertisement
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