在SQL Server中将数据类型nvarchar转换为bit时出错 [英] Getting error error converting data type nvarchar to bit in SQL server

问题描述

我试图更新记录它给出错误错误将数据类型nvarchar转换为位



我尝试过：

I m trying to update the record its giving error Error converting data type nvarchar to bit

What I have tried:

ALTER Procedure [dbo].[sp_Insert_UpDateTypeTable]
(
    @AppNo nvarchar(max),
	@Unit VarChar(10),
	@Post VarChar(10),
	@MPR_RejectionReason MPR_RejectionReason READONLY,
	@MPR_ScrutinyDocument MPR_ScrutinyDocument READONLY,
	@DV_Education_ID int,
	@DV_Type_ID int=0,
	@DV_Elligible_NonElligible bit=0,
	@DV_FromNaxaliteArea bit=0,	
	@DV_Hsc_Ssc_7th_Marks int=0,
	@DV_Cast nvarchar(max)=null,
	@DV_Category_ID int=0,
	@DV_CreatedBy uniqueidentifier= null--,
	-
	
)
As
Begin


	Declare @SQL varchar(Max), @Columns varchar(Max), @quote char(1)=char(39),@unq uniqueidentifier
	
	set @unq=NEWID()

	Create Table #MPR_RejectionReason
	(
		ApplicationNo nvarchar(50) Not Null,
		DocRejectionReason_ID int not Null
	)

	Insert Into #MPR_RejectionReason
	select * from @MPR_RejectionReason

	Create Table #MPR_ScrutinyDocument
	(
		ApplicationNo nvarchar(50) Not Null,
		[ScrutinyDocument_ID] int not Null,
		IsExist bit null,
		Invalid bit null,
		Remarks nvarchar(max) null
	)

	Insert Into #MPR_ScrutinyDocument
	select * from @MPR_ScrutinyDocument


	Select @Columns = N'Set DV_Education_ID =' + convert(varchar(20),@DV_Education_ID) + ',
		DV_Type_ID ='+ convert(varchar(20),@DV_Type_ID) +',
		DV_Elligible_NonElligible ='+  convert(varchar(20),@DV_Elligible_NonElligible) +',
		DV_FromNaxaliteArea ='+  convert(varchar(20),@DV_FromNaxaliteArea)+',
		DV_Hsc_Ssc_7th_Marks ='+ convert(varchar,@DV_Hsc_Ssc_7th_Marks) +',
		DV_Cast =' + @quote + @DV_Cast + @quote +',
		DV_Category_ID ='+ convert(varchar(20),@DV_Category_ID) --+',
		--DV_CreatedBy =
  --      (
  --          CASE DV_CreatedBy
  --              WHEN
  --                  DV_CreatedBy is null
  --              THEN
  --                  '+@quote+convert(varchar(50),@DV_CreatedBy)+@quote+'
  --              ELSE
  --                  DV_CreatedBy
  --          END
  --      )'+',

		--DV_CreatedOn=
		--(
  --          CASE DV_CreatedOn
  --              WHEN
  --                  DV_CreatedOn is null
  --              THEN
  --                  getdate()
  --              ELSE
  --                  DV_CreatedOn
  --          END
  --      ),

		


		--DV_UpdatedBy=
  --          (CASE 
  --              WHEN
  --                  DV_CreatedBy is null
  --              THEN
  --                 DV_UpdatedBy
  --              ELSE
  --                '+@quote+ convert(varchar(50),@DV_CreatedBy)+@quote+'
		--		END'
  --      +',

		--DV_UpdatedOn=
  --         ( CASE 
  --              WHEN
  --                  CreatedOn is null
  --              THEN
  --                 DV_UpdatedOn
  --              ELSE
  --                getdate()
  --          END
  --      )'
		

		
		Select @SQL=N'/*Main Table*/
				Update ' + TableName + ' ' + @Columns + ' 
				Where AppNo=' + @quote + @AppNo + @quote + '

				/*Delete Tran tables*/
				Delete From ' + TableName + '_RejectionReason
				Where AppNo=' + @quote + @AppNo + @quote + '
				
				Delete From ' + TableName + '_ScrutinyDocument
				Where AppNo=' + @quote + @AppNo + @quote + '

				/*Insert Tran tables*/
				INSERT INTO ' + TableName + '_RejectionReason
				select * from #MPR_RejectionReason '+


				'INSERT INTO ' + TableName + '_ScrutinyDocument
				select * from #MPR_ScrutinyDocument' +

				'/*Insert History tables*/'+

               
				'Insert INTO MPR_DocVeriHistory(Unq,AppNo,Unit,Post,DV_Education_ID,DV_FromNaxaliteArea,DV_Hsc_Ssc_7th_Marks,DV_CreatedBy,DV_CreatedOn) values'+
				'('+@quote+convert(varchar(50),@unq) + @quote+',N'+@quote+@AppNo+@quote+','+@quote+@Unit+@quote+','+@quote+@Post+@quote+','+@DV_Education_ID+','+@DV_FromNaxaliteArea+','+@DV_Hsc_Ssc_7th_Marks+','+@quote+convert(varchar(50),@DV_CreatedBy)+@quote+',getdate())'+


				
				'Insert INTO MPR_ScrutinyDocumentHistory(Unq, AppNo, ScrutinyDocument_ID, IsExist, Invalid, Remarks)
				select'+@quote +convert(varchar(50),@unq) + @quote+', AppNo, ScrutinyDocument_ID, IsExist, Invalid, Remarks from ' + TableName + '_ScrutinyDocument
				Where AppNo=' + @quote + @AppNo + @quote +

				'Insert INTO MPR_RejectionReasonHistory(Unq, AppNo, DocRejectionReason_ID)
				select'+@quote +convert(varchar(50),@unq) + @quote+', AppNo, DocRejectionReason_ID from ' + TableName + '_RejectionReason
				Where AppNo=' + @quote + @AppNo + @quote

 
	   	from MPR_TableList where Unit='01' and Post= '01'
		print @Columns
		print @SQL
	Exec(@SQL)
	DROP table #MPR_RejectionReason
	DROP table #MPR_ScrutinyDocument

End

推荐答案

首先你要更改行

First of all you should change the row

DV_FromNaxaliteArea ='+  convert(varchar(20),@DV_FromNaxaliteArea)+',

to

to

DV_FromNaxaliteArea ='+  convert(bit,@DV_FromNaxaliteArea)+',

同时确保c @DV_Fro​​mNaxaliteArea 的内容可以转换为可能只包含'NULL'，'0'或'1'的位。

Also ensure that the content of @DV_FromNaxaliteArea is something that can be converted to bit which may only contain 'NULL', '0' or '1'.

这篇关于在SQL Server中将数据类型nvarchar转换为bit时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！