this is my store procedure.. any one can suggst me .. how to pass districtid from query string .. I use this for fatch query string value..
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.Connection = connection;
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.CommandText = "spGetCHLDETAIL";
sqlCmd.Parameters.AddWithValue("@barcode", Request.QueryString["CHILDCODE"]);
sqlCmd.Parameters.AddWithValue("@DistrictID", Request.QueryString["DIST_ID"]);
i want to use this DIST_ID,,CHILDCODE in my store procedure
please help
USE [SCJ]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spGetCHLDETAIL]
(
@DistrictID VARCHAR(250),
@barcode VARCHAR(50)
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @FirstRow AS INT,
@LastRow AS INT,
@ChildTblName AS VARCHAR (125),
@FamilyTblName AS VARCHAR (125),
@vQuery AS NVARCHAR (MAX);
SET @ChildTblName = '[' + @DistrictID + 'Child_D' + ']';
SET @FamilyTblName = '[' +@DistrictID + 'Family_D' + ']'
CREATE TABLE #tblChildFamily
(
scanno NVARCHAR (255),
distt NVARCHAR (255),
block NVARCHAR (255),
gpmctc NVARCHAR (255),
village NVARCHAR (255),
habitation NVARCHAR (255),
location NVARCHAR (255),
residing NVARCHAR (255),
mother_ton NVARCHAR (255),
pincode NVARCHAR (255),
hhno NVARCHAR (255),
barcode NVARCHAR(255),
fname NVARCHAR (255),
mname NVARCHAR (255),
gname NVARCHAR (255),
sex NVARCHAR (255),
dob NVARCHAR (255),
social_grp NVARCHAR (255),
minority NVARCHAR (255),
total_male NVARCHAR (255),
total_fema NVARCHAR (255),
seasonal_m NVARCHAR (255),
migration_ NVARCHAR (255),
duration NVARCHAR (255),
lp NVARCHAR (255),
up NVARCHAR (255),
sec NVARCHAR (255),
catch_s_lp NVARCHAR (255),
catch_s_up NVARCHAR (255),
catch_s_se NVARCHAR (255),
cname NVARCHAR (255),
class NVARCHAR (255),
disability NVARCHAR (255),
sch_code NVARCHAR (255),
nereason NVARCHAR (255),
droupout NVARCHAR (255),
highclass NVARCHAR (255),
uid NVARCHAR(255),
);
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,Child.uid
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 + '';
INSERT INTO #tblChildFamily (scanno, distt, block, gpmctc, village,habitation,location,residing,mother_ton,pincode,hhno,barcode,fname,mname,gname,sex,dob,social_grp,minority,total_male,total_fema,seasonal_m,migration_,duration,lp,up,sec,catch_s_lp,catch_s_up,catch_s_se,cname,class,disability,sch_code,nereason,droupout, highclass,uid)
EXECUTE sp_executesql @vQuery;
WITH PagedGetFilteredChild
AS (SELECT child.scanno,Child.barcode,Child.gpmctc,Child.habitation,Child.location,Child.residing,Child.mother_ton,Child.disability,Child.sch_code,Child.class,Child.nereason,Child.droupout,Child.pincode,Child.hhno,Child.fname,Child.mname,Child.gname,Child.social_grp,Child.minority,Child.total_male,Child.total_fema,Child.seasonal_m,Child.migration_,Child.duration,Child.lp,Child.up,Child.sec,child.catch_s_lp,child.catch_s_up,Child.catch_s_se,
Child.uid AS [Child UID],
child.cname AS [Child Name],
Dist.distname AS [District Name],
Child.distt AS [distc ID],
(SELECT blockname
FROM dbo.BLOCK WITH (NOLOCK)
WHERE distt = CHILD.distt
AND block = Child.block) AS [Block Name],
(SELECT vilname
FROM dbo.VilMaster WITH (NOLOCK)
WHERE vilcode = Child.Village AND VilMaster.dist = CHILD.distt AND block = Child.block
) AS [Village Name],
'' AS [School Name],
Child.barcode AS [Child Code],
child.sex AS [Sex],
child.dob AS [Age],
Child.fname AS [Father's Name],
Child.mname AS [Mother's Name]
FROM #tblChildFamily AS Child WITH (NOLOCK)
INNER JOIN
dbo.DIST AS Dist WITH (NOLOCK)
ON Child.distt = Dist.distt)
SELECT scanno,barcode,gpmctc,habitation,location,residing,mother_ton,disability,sch_code,class,nereason,droupout,pincode,hhno,fname,mname,gname,social_grp,minority,total_male,total_fema,seasonal_m,migration_,duration,lp,up,sec,catch_s_lp,catch_s_up,catch_s_se, [Child Name] AS ChildName,
[District Name] AS DistrictName,
[distc ID] as districtID,
[Block Name] AS BlockName,
[Village Name] AS VillageName,
[Child UID] AS [ChildUID],
[Sex] AS Sex,
[Age] AS Age,
[Father's Name] AS FatherName,
[Mother's Name] AS MotherName
FROM PagedGetFilteredChild where barcode=@barcode
ORDER BY [Child Name];
IF OBJECT_ID('tempdb..#tblChildFamily') IS NOT NULL
DROP TABLE #tblChildFamily;
END