hi. Try this. I think you left single inverted comma (') in where clause
USE [SCJ]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <author,,name>
-- Create date: <create>
-- Description: <description,,>
-- =============================================
ALTER PROCEDURE [dbo].[spGetCHLDETAIL1]
-- Add the parameters for the stored procedure here
@DistrictID VARCHAR(250),
@barcode VARCHAR(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE
@ChildTblName AS VARCHAR (125),
@FamilyTblName AS VARCHAR (125),
@vquery1 AS varchar(max),
@vQuery AS VARCHAR (max);
SET @ChildTblName = '[' + @DistrictID + 'Child_D' + ']'
SET @FamilyTblName = '[' + @DistrictID + 'Family_D' + ']'
-- Insert statements for procedure here
SET @vQuery = 'SELECT Child.scanno,Child.cname,Child.distt,Child.block,Child.gpmctc,Child.village ,Child.habitation, Child.barcode,Child.sex,Child.dob,Family.pincode,Family.hhno,Family.fname,Family.mname,Family.gname,Family.social_grp,Family.minority,Family.total_male,Family.total_fema,Family.seasonal_m,Family.migration_,Family.duration,Family.lp,Family.up,Family.sec,Family.catch_s_lp,Family.catch_s_up,Family.catch_s_se,Child.location,
Child.residing,Child.mother_ton,Child.disability,Child.sch_code,Child.class,Child.nereason,Child.droupout,Child.highclass
FROM ' + @ChildTblName + ' As Child WITH (NOLOCK) INNER JOIN ' + @FamilyTblName + ' AS Family WITH (NOLOCK) ON
Child.barcode=Family.barcode WHERE LTRIM(RTRIM(Child.cname)) <> '''' AND Child.distt = ''' + @DistrictID + '''';
-- EXEC(@vQuery)
you can use print to check the statement
--print @vQuery
EXEC(@vQuery)
-- exec(@vquery1)
--insert into CHLDINFO_LOG(Chld_Name) values(@vquery)