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