带参数的SQL Server查询 [英] Sql server query with parameter

问题描述

我有一个查询，我想在数据集中用于在asp.net中生成rldc报告。查询是

 选择 schoolname，schoolid，count（studentid） as  studenttotals，年龄，性别来自学生其中​​ 左（studentid， 4 ）=  2006  

。查询生成正确的结果。 studentid从一年开始，因此在这个查询中，我想要所有ID为2006年的学生。现在我的挑战是在哪里（studentid，4）= 2006，我怎样才能创建参数以便我可以输入任何一年我的选择让我说我想要那些从2007年开始的人; 2008;我想在报告生成期间通过下拉列表输入参数。

解决方案

 创建您的SP（存储过程）在 SP中，您可以编写相同的选择查询和 添加参数到 SP。 你的朋友 end 调用SP 和传递参数 ex 
 
  create   procedure  [dbo]。[USP_School_Details 选择] 
  @ Years   varchar （ 10 ）; 
  AS  
  BEGIN  
 
 schoolname，schoolid， count（studentid） as  studenttotals，年龄，性别来自学生其中  left （studentid， 4 ）=  @Years  
 
 
 
  END  
  exec  USP_School_Details 选择 '  2007'< /跨度> 

I have got a query that i want to use in a dataset for producing rldc reports in asp.net. The query is

select schoolname, schoolid, count(studentid) as studenttotals,age,sex from student where left(studentid,4) = 2006

. The query generates correct results. The studentid starts with a year hence in this query i want all students with an id starting with year 2006. Now my challenge is on where left(studentid,4) = 2006, how can i make a parameter so that i can enter any year of my choice lets say i want those whose id start with 2007;2008; etc. I want the parameter to be entered through dropdownlist during report generation.

解决方案

Create your SP(Stored procedure) In SP you can write your same select query and add parameter to the SP.in your frond end call the SP and pass the parameter for ex

create procedure [dbo].[USP_School_Details Select]
    @Years  varchar(10);
AS
BEGIN

schoolname, schoolid, count(studentid) as studenttotals,age,sex from student where left(studentid,4) = @Years



END
exec  USP_School_Details Select '2007'

这篇关于带参数的SQL Server查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！