如何在sql store过程中使用查询字符串值。 [英] how to use query string value in sql store procedure.
本文介绍了如何在sql store过程中使用查询字符串值。的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
这是我的商店程序..任何人都可以建议我..如何从查询字符串传递districtid ..我用这个来获取查询字符串值..
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想在我的商店程序中使用这个DIST_ID ,, CHILDCODE
请帮助< br $>
i want to use this DIST_ID,,CHILDCODE in my store procedure
please help
USE [SCJ]
GO
/****** Object: StoredProcedure [dbo].[spGetCHLDETAIL] Script Date: 03/03/2014 14:32:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- EXEC spGetFilteredChild '12','','','','','','','','','','','','','5','','','','','','','','',1,100
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]
--Child.highclass AS [Child UID],
--Child.uid AS [Child UID],
--Child.barcode AS [Child UID],
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
推荐答案
你已经在你的@districtid中传递了请求参数。请指出你的问题。谢谢！
You already passed @districtid in your query string. Please point your problem. Thanks!
试试这个
sqlCmd.Parameters.AddWithValue（@ barcode，Request.QueryString [CHILDCODE]。Tostring（））;
sqlCmd.Parameters.AddWithValue（@ DistrictID，Request.QueryString [DIST_ID]。Tostring（））;
try this
sqlCmd.Parameters.AddWithValue("@barcode", Request.QueryString["CHILDCODE"].Tostring());
sqlCmd.Parameters.AddWithValue("@DistrictID", Request.QueryString["DIST_ID"].Tostring());
这篇关于如何在sql store过程中使用查询字符串值。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
