how can i execute query with more than 4000 character

Question

Hi,

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)