I am writing a dynamic procedure.When i execute for update this is truncate my query.Please find procedure and message below
ALTER PROCEDURE [sbm].[SBM_InsertUpdate_BenfMasterData]
@paramBenefID int=-1
, @paramStateID int
, @paramDistrictID int
, @paramBlockID int
, @paramGramPanchayatID int
, @paramVillageID int
, @paramHabitationID int
, @paramFinYear varchar(9)
, @paramFamilyHead varchar(50)
, @paramFatherHusbandName varchar(50)
, @paramFamilyHeadLocal nvarchar(50)
, @paramFatherHusbandNameLocal nvarchar(50)
, @paramGender char(1)
, @paramCardType varchar(2)
, @paramCardNumber varchar(35)
, @paramCategory varchar(3)
, @paramSubCategory varchar(2)
, @paramAdhaarCard varchar(12)
, @paramMobile varchar(10)
, @paramToiletConstructedFrom varchar(11)
, @paramIsHavingToilet varchar(2)
, @paramIsHavingToiletFunctional varchar(2)
, @paramIsHavingToiletFunctionalUsed varchar(2)
, @paramIsWaterFacilityAvailable varchar(2)
, @paramEntryUser varchar(25)
, @paramEntryDate smalldatetime
, @paramStateLevelUniqueBenefId varchar(25)
, @flag varchar(2)='-1' output --Flag is used as a Output Parameter
AS
BEGIN
IF @paramEntryDate IS NULL BEGIN
SELECT @paramEntryDate=CAST(getDate() as SMALLDATETIME)
END
DECLARE @ParamOutput nvarchar(100)
SET @ParamOutput=N'@flag varchar(2) OUTPUT';
DECLARE @strQuery nvarchar(MAX)
DECLARE @strQuery1 nvarchar(MAX)
SET @strQuery=N' DECLARE @Lflag varchar(2)
DECLARE @RowcountInsert int
SELECT @RowcountInsert=count(GramPanchayatID) FROM SBM_BenfMasterData_'+ CONVERT(varchar(2),@paramStateID)+' WHERE [BenefId]='''+CONVERT(varchar,@paramBenefID)+''' AND [GramPanchayatID]='''+CONVERT(varchar,@paramGramPanchayatID)+'''
DECLARE @isinsert varchar(3); set @isinsert=0;
IF @RowcountInsert=0 BEGIN
IF '''+@paramIsHavingToilet+'''=''N'' BEGIN
SELECT @isinsert=count(*) from SBM_DistrictwiseBeneficiaryCAP WHERE [DistrictID]='''+CONVERT(varchar,@paramDistrictID)+''' and TotalHHDetailEntered < TotalHHCAP and HHWithoutToiletDetailEntered < HHWithoutToiletCAP
END
IF '''+@paramIsHavingToilet+'''=''Y'' BEGIN
SELECT @isinsert=count(*) from SBM_DistrictwiseBeneficiaryCAP WHERE [DistrictID]='''+CONVERT(varchar,@paramDistrictID)+''' and TotalHHDetailEntered < TotalHHCAP and HHWithToiletDetailEntered < HHWithToiletCAP
END
INSERT INTO SBM_BenfMasterData_'+ CONVERT(varchar(2),@paramStateID)+'
( StateID,DistrictID,BlockID,GramPanchayatID,VillageID,HabitationID,FinYear,FamilyHead,FatherHusbandName,
FamilyHeadLocal,FatherHusbandNameLocal,Gender,CardType,CardNumber,Category,SubCategory,AdhaarCard,Mobile,ToiletConstructedFrom,
IsHavingToilet,IsHavingToiletFunctional,IsHavingToiletFunctionalUsed,IsWaterFacilityAvailable,EntryUser,EntryDate,StateLevelUniqueBenefId
) VALUES
( '''+CONVERT(varchar,@paramStateID)+''','''+CONVERT(varchar,@paramDistrictID)+''','''+CONVERT(varchar,@paramBlockID)+''','''+CONVERT(varchar,@paramGramPanchayatID)+''','''+CONVERT(varchar,@paramVillageID)+''','''+CONVERT(varchar,@paramHabitationID)+''',
'''+@paramFinYear+''','''+@paramFamilyHead+''','''+@paramFatherHusbandName+''','''+@paramFamilyHeadLocal+''','''+@paramFatherHusbandNameLocal+''',
'''+@paramGender+''','''+@paramCardType+''','''+@paramCardNumber+''','''+@paramCategory+''','''+@paramSubCategory+''','''+@paramAdhaarCard+''',
'''+@paramMobile+''','''+@paramToiletConstructedFrom+''','''+@paramIsHavingToilet+''','''+@paramIsHavingToiletFunctional+''',
'''+@paramIsHavingToiletFunctionalUsed+''','''+@paramIsWaterFacilityAvailable+''','''+@paramEntryUser+''','''+CONVERT(varchar,@paramEntryDate)+''','''+@paramStateLevelUniqueBenefId+''')
IF '''+@paramIsHavingToilet+'''=''N'' and @isinsert > 0 BEGIN
UPDATE SBM_DistrictwiseBeneficiaryCAP SET HHWithoutToiletDetailEntered=(HHWithoutToiletDetailEntered+1),TotalHHDetailEntered=(TotalHHDetailEntered+1) where [DistrictID]='''+CONVERT(varchar,@paramDistrictID)+'''
END
IF '''+@paramIsHavingToilet+'''=''Y'' and @isinsert > 0 BEGIN
UPDATE SBM_DistrictwiseBeneficiaryCAP SET HHWithToiletDetailEntered=(HHWithToiletDetailEntered+1),TotalHHDetailEntered=(TotalHHDetailEntered+1) where [DistrictID]='''+CONVERT(varchar,@paramDistrictID)+'''
END
IF ('+convert(varchar(10),@@rowcount)+'>0)BEGIN
SET @Lflag =0
END
ELSE BEGIN
SET @Lflag =4
END
END '
set @strQuery1=N'
ELSE IF @RowcountInsert>0 BEGIN
DECLARE @isupdate char(1)
DECLARE @IsHavingTlt char(1)
SET @isupdate =0
SELECT @IsHavingTlt=IsHavingToilet FROM SBM_BenfMasterData_'+ CONVERT(varchar(2),@paramStateID)+' WHERE [BenefID]='''+CONVERT(varchar,@paramBenefID)+''' and GramPanchayatID='''+CONVERT(varchar,@paramGramPanchayatID )+'''
IF @IsHavingTlt =''N'' and '''+@paramIsHavingToilet+'''=''Y'' BEGIN
SELECT @isupdate=count(*) from SBM_DistrictwiseBeneficiaryCAP WHERE [DistrictID]='''+CONVERT(varchar,@paramDistrictID)+''' and (HHWithToiletDetailEntered+1) <= HHWithToiletCAP
END
ELSE IF @IsHavingTlt =''Y'' and '''+@paramIsHavingToilet+'''=''N'' BEGIN
SELECT @isupdate=count(*) from SBM_DistrictwiseBeneficiaryCAP WHERE [DistrictID]='''+CONVERT(varchar,@paramDistrictID)+''' and (HHWithoutToiletDetailEntered+1) <= HHWithoutToiletCAP
END
ELSE BEGIN
SET @isupdate=5
END
UPDATE SBM_BenfMasterData_'+ CONVERT(varchar(2),@paramStateID)+'
SET [StateID]='''+ CONVERT(varchar(2),@paramStateID)+'''
,[DistrictID]='''+CONVERT(varchar,@paramDistrictID)+'''
,[BlockID]='''+CONVERT(varchar,@paramBlockID)+'''
,[VillageID]='''+CONVERT(varchar,@paramVillageID)+'''
,[HabitationID]='''+CONVERT(varchar,@paramHabitationID)+'''
,[FinYear]='''+@paramFinYear+'''
,[FamilyHead]='''+@paramFamilyHead+'''
,[FatherHusbandName]='''+@paramFatherHusbandName+'''
,[FamilyHeadLocal]='''+@paramFamilyHeadLocal+'''
,[FatherHusbandNameLocal]='''+@paramFatherHusbandNameLocal+'''
,[Gender]='''+@paramGender+'''
,[CardType]='''+@paramCardType+'''
,[CardNumber]='''+@paramCardNumber+'''
,[Category]='''+@paramCategory+'''
,[SubCategory]='''+@paramSubCategory+'''
,[AdhaarCard]='''+@paramAdhaarCard+'''
,[Mobile]='''+@paramMobile+'''
,[ToiletConstructedFrom]='''+@paramToiletConstructedFrom+'''
,[IsHavingToilet]='''+@paramIsHavingToilet+'''
,[IsHavingToiletFunctional]='''+@paramIsHavingToiletFunctional+'''
,[IsHavingToiletFunctionalUsed]='''+@paramIsHavingToiletFunctionalUsed+'''
,[IsWaterFacilityAvailable]='''+@paramIsWaterFacilityAvailable+'''
,[UpdateUser]='''+@paramEntryUser+'''
,[UpdateDate]='''+CONVERT(varchar,@paramEntryDate)+'''
,[StateLevelUniqueBenefId]='''+@paramStateLevelUniqueBenefId+'''
WHERE [BenefID]='''+CONVERT(varchar,@paramBenefID)+''' AND [GramPanchayatID]='''+CONVERT(varchar,@paramGramPanchayatID)+'''
SET @Lflag =''1''
IF @IsHavingTlt =''N'' and '''+@paramIsHavingToilet+'''=''Y'' AND @isupdate!=5 BEGIN
UPDATE SBM_DistrictwiseBeneficiaryCAP SET HHWithoutToiletDetailEntered=(HHWithoutToiletDetailEntered-1),HHWithToiletDetailEntered=(HHWithToiletDetailEntered+1) WHERE [DistrictID]='''+CONVERT(varchar,@paramDistrictID)+''' and HHWithToiletCAP <= (HHWithToiletDetailEntered+1)
END
ELSE IF @IsHavingTlt =''Y'' and '''+@paramIsHavingToilet+'''=''N'' AND @isupdate!=5 BEGIN
UPDATE SBM_DistrictwiseBeneficiaryCAP SET HHWithoutToiletDetailEntered=(HHWithoutToiletDetailEntered+1),HHWithToiletDetailEntered=(HHWithToiletDetailEntered-1) WHERE [DistrictID]='''+CONVERT(varchar,@paramDistrictID)+''' and HHWithoutToiletCAP <= (HHWithoutToiletDetailEntered+1)
END
END
ELSE BEGIN
SET @Lflag =''2''
END
print @Lflag
SELECT @flag=@Lflag
'
DECLARE @strf NVARCHAR(MAX);
SET @strf=@strQuery+@strQuery1;
PRINT @strf
EXEC sp_executesql @strf , @ParamOutput, @flag OUTPUT
SELECT @flag
END
DECLARE @Lflag varchar(2)
DECLARE @RowcountInsert int
SELECT @RowcountInsert=count(GramPanchayatID) FROM SBM_BenfMasterData_1 WHERE [BenefId]='183120854' AND [GramPanchayatID]='201698'
DECLARE @isinsert varchar(3); set @isinsert=0;
IF @RowcountInsert=0 BEGIN
IF 'N'='N' BEGIN
SELECT @isinsert=count(*) from SBM_DistrictwiseBeneficiaryCAP WHERE [DistrictID]='4' and TotalHHDetailEntered < TotalHHCAP and HHWithoutToiletDetailEntered < HHWithoutToiletCAP
END
IF 'N'='Y' BEGIN
SELECT @isinsert=count(*) from SBM_DistrictwiseBeneficiaryCAP WHERE [DistrictID]='4' and TotalHHDetailEntered < TotalHHCAP and HHWithToiletDetailEntered < HHWithToiletCAP
END
INSERT INTO SBM_BenfMasterData_1
( StateID,DistrictID,BlockID,GramPanchayatID,VillageID,HabitationID,FinYear,FamilyHead,FatherHusbandName,
FamilyHeadLocal,FatherHusbandNameLocal,Gender,CardType,CardNumber,Category,SubCategory,AdhaarCard,Mobile,ToiletConstructedFrom,
IsHavingToilet,IsHavingToiletFunctional,IsHavingToiletFunctionalUsed,IsWaterFacilityAvailable,EntryUser,EntryDate,StateLevelUniqueBenefId
) VALUES
( '1','4','4865','201698','589043','18888',
'2015-2016','Suresh ',' Chandra','','',
'F','04','WAP100503200395','BPL','01','',
'9490252894','NA','N','NA',
'NA','NA','admin','Sep 21 2015 12:23AM','')
IF 'N'='N' and @isinsert > 0 BEGIN
UPDATE SBM_DistrictwiseBeneficiaryCAP SET HHWithoutToiletDetailEntered=(HHWithoutToiletDetailEntered+1),TotalHHDetailEntered=(TotalHHDetailEntered+1) where [DistrictID]='4'
END
IF 'N'='Y' and @isinsert > 0 BEGIN
UPDATE SBM_DistrictwiseBeneficiaryCAP SET HHWithToiletDetailEntered=(HHWithToiletDetailEntered+1),TotalHHDetailEntered=(TotalHHDetailEntered+1) where [DistrictID]='4'
END
IF (1>0)BEGIN
SET @Lflag =0
END
ELSE BEGIN
SET @Lflag =4
END
END
ELSE IF @RowcountInsert>0 BEGIN
DECLARE @isupdate char(1)
DECLARE @IsHavingTlt char(1)
SET @isupdate =0
SELECT @IsHavingTlt=IsHavingToilet FROM SBM_BenfMasterData_1 WHERE [BenefID]='183120854' and GramPanchayatID='201698'
IF @IsHavingTlt ='N' and 'N'='Y' BEGIN
SELECT @isupdate=count(*) from SBM_DistrictwiseBeneficiaryCAP WHERE [DistrictID]='4' and (HHWithToiletDetailEntered+1) <= HHWithToiletCAP
END
ELSE IF @IsHavingTlt ='Y' and 'N'='N' BEGIN
SELECT @isupdate=count(*) from SBM_DistrictwiseBeneficiaryCAP WHERE [DistrictID]='4' and (HHWithoutToiletDetailEntered+1) <= HHWithoutToiletCAP
END
ELSE BEGIN
SET @isupdate=5
END
UPDATE SBM_BenfMasterData_1
SET [StateID]='1'
,[DistrictID]='4'
,[BlockID]='4865'
,[VillageID]='589043'
,[HabitationID]='18888'
,[FinYear]='2015-2016'
,[FamilyHead]='Suresh '
,[FatherHusbandName]=' Chandra'
,[FamilyHeadLocal]=''
,[FatherHusbandNameLocal]=''
,[Gender]='F'
,[CardType]='04'
,[CardNumber]='WAP100503200395'
,[Category]='BPL'
,[SubCategory]='01'
,[AdhaarCard]=''
,[Mobile]='9490252894'
,[ToiletConstructedFrom]='NA'
,[IsHavingToilet]='N'
,[IsHavingToiletFunctional]='NA'
,[IsHavingToiletFunctionalUsed]='NA'
,[IsWaterFacilityAvailable]='NA'
,[UpdateUser]='admin'
,[UpdateDate]='Sep 21 2015 12:23AM'
,[StateLevelUniqueBenefId]=''
WHERE [BenefID]='183120854' AND [GramPanchayatID]='201698'
SET @Lflag ='1'
IF @IsHavingTlt ='N' and 'N'='Y' AND @isupdate!=5 BEGIN
UPDATE SBM_DistrictwiseBeneficiaryCAP SET HHWithoutToiletDetailEntered=(HHWithoutToiletDetailEntered-1),HHWithToiletDetailEntered=(HHWithToiletDetailEntered+1) WHERE [DistrictID]='4' and HHWithToiletCAP <= (HHWithToiletDetailEntered+1)
END
ELSE IF @IsHavingTlt ='Y' and 'N'='N' AND @isupdate!=5 BEGIN
UPDATE SBM_DistrictwiseBeneficiaryCAP SET HHWithoutToiletDetailEntered=(HHWithoutToiletDetailEntered+1),HHWithToiletDetailEntered=(HHWithToiletDetailEntered-1)
(1 row(s) affected)
1
(1 row(s) affected)