SqlServer 多條件查詢 [預存程序] 經典例子結合C#的使用

表中的欄位：

CREATE TABLE [dbo].[stuInfo] ([FNumber] [int] IDENTITY(1,1) NOT NULL ,[FName] [nvarchar] (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,[FNameen] [varchar] (35) COLLATE Chinese_PRC_CI_AS  ,[FSex] [char] (1) COLLATE Chinese_PRC_CI_AS NOT NULL ,[FEducation] [char] (1) COLLATE Chinese_PRC_CI_AS NOT NULL ,[FCardID] [char] (18) COLLATE Chinese_PRC_CI_AS NOT NULL ,[FBirDate] [datetime] NOT NULL ,[FTech] [nvarchar](50) COLLATE Chinese_PRC_CI_AS ,[FSubject] [nvarchar](50) COLLATE Chinese_PRC_CI_AS,[FJiF] [nvarchar](100)COLLATE Chinese_PRC_CI_AS,[FJobAdd][nvarchar](100)COLLATE Chinese_PRC_CI_AS,[FExamNum][varchar](30)COLLATE Chinese_PRC_CI_AS NOT NULL,[FServerNum] [varchar] (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,[FExamDate] [datetime] NOT NULL ,[FIsAE] [char] (1) COLLATE Chinese_PRC_CI_AS NOT NULL ) ON [PRIMARY]GO

預存程序：

IF EXISTS(SELECT *FROM SYSOBJECTS WHERE NAME='PROC_Look')DROP PROC  PROC_LookGOCREATE PROC PROC_Look@FName  NVARCHAR(30)=NULL,@FNameen VARCHAR(35)=NULL,@FCardID CHAR(18)=NULL,@FExamNum NVARCHAR(30)=NULL,@FServerNum NVARCHAR(30)=NULL,@FSex  CHAR(1)=NULL,@FEducation CHAR(1)=NULL,@FIsAE  CHAR(1)=NULL,@FTech  NVARCHAR(50)=NULL,@FSubject NVARCHAR(50)=NULL,@FJiF  NVARCHAR(100)=NULL,@FJobAdd NVARCHAR(100)=NULL,@FStartTime DATETIME=NULL,@FEndTime DATETIME=NULL  ASdeclare @sqlStr varchar(100)        if @FName IS NOT NULLbegin  set @sqlStr=' where FName='+''''+@FName+'''' end        if @FNameen IS NOT NULLbegin    if @sqlStr IS NOT NULL        set @sqlStr=@sqlStr+' and FNameen='+''''+@FNameen+''''     else  set @sqlStr=' where FNameen='+''''+@FNameen+''''end    if @FCardID IS NOT NULLbegin  if @sqlStr IS NOT NULL  set @sqlStr=@sqlStr+' and FCardID='+''''+@FCardID+''''   else  set @sqlStr=' where FCardID='+''''+@FCardID+''''endif @FExamNum IS NOT NULLbegin  if @sqlStr IS NOT NULL  set @sqlStr=@sqlStr+' and FExamNum='+''''+@FExamNum+''''  else  set @sqlStr=' where FExamNum='+''''+@FExamNum +''''endif @FServerNum IS NOT NULLbegin  if @sqlStr IS NOT NULL  set @sqlStr=@sqlStr+' and FServerNum='+''''+@FServerNum+''''  else  set @sqlStr=' where FServerNum='+''''+@FServerNum+''''endif @FSex IS NOT NULLbegin  if @sqlStr IS NOT NULL  set @sqlStr=@sqlStr+' and FSex='+''''+@FSex+''''  else  set @sqlStr=' where FSex='+''''+@FSex+''''endif @FEducation IS NOT NULLbegin  if @sqlStr IS NOT NULL  set @sqlStr=@sqlStr+' and FEducation='+''''+@FEducation+''''                                                  else  set @sqlStr=' where FEducation='+''''+@FEducation+''''  end if @FIsAE IS NOT NULLbegin  if @sqlStr IS NOT NULL  set @sqlStr=@sqlStr+' and FIsAE='+''''+@FIsAE+''''                          else  set @sqlStr=' where FIsAE='+''''+@FIsAE+''''                        endif @FTech IS NOT NULLbegin  if @sqlStr IS NOT NULL  set @sqlStr=@sqlStr+' and FTech='+''''+@FTech+''''  else  set @sqlStr=' where FTech='+''''+@FTech+''''endif @FSubject IS NOT NULL  begin  if @sqlStr IS NOT NULL    set @sqlStr=@sqlStr+' and FSubject='+''''+@FSubject+''''  else    set @sqlStr=' where FSubject='+''''+@FSubject +''''  endif @FJiF IS NOT NULLbegin  if @sqlStr IS NOT NULL  set @sqlStr=@sqlStr+' and FJiF='+''''+@FJiF+''''   else  set @sqlStr=' where FJiF='+''''+@FJiF+''''endif @FJobAdd IS NOT NULLbegin  if @sqlStr IS NOT NULL  set @sqlStr=@sqlStr+' and FJobAdd='+''''+@FJobAdd +''''  else  set @sqlStr=' where FJobAdd='+''''+@FJobAdd +''''endEXEC('select *from stuInfo '+ @sqlStr)GO

可以在SQL SERVER查詢分析器裡，執行該預存程序並跟蹤@sqlStr

EXEC PROC_LOOK Herbert

下面的代碼示範如何在C#裡面使用上面的預存程序

string source = "Server = (local); Database = Students; Integrated Security = SSPI";            SqlConnection conn = new SqlConnection(source);            conn.Open();            SqlCommand cmd = new SqlCommand("PROC_Look", conn);            cmd.CommandType = CommandType.StoredProcedure;            cmd.Parameters.Add(new SqlParameter("@FName", SqlDbType.NVarChar, 30, "FName"));                        cmd.Parameters.Add(new SqlParameter("@FNameen", SqlDbType.NVarChar, 35, "FNameen"));            cmd.Parameters.Add(new SqlParameter("@FCardID", SqlDbType.Char, 18, "FCardID"));            cmd.Parameters.Add(new SqlParameter("@FExamNum", SqlDbType.NVarChar, 30, "FExamNum"));            cmd.Parameters.Add(new SqlParameter("@FServerNum", SqlDbType.NVarChar, 30, "FServerNum"));            cmd.Parameters.Add(new SqlParameter("@FSex", SqlDbType.Char, 1, "FSex"));            cmd.Parameters.Add(new SqlParameter("@FEducation", SqlDbType.Char, 1, "FEducation"));            cmd.Parameters.Add(new SqlParameter("@FIsAE", SqlDbType.Char, 1, "FIsAE"));            cmd.Parameters.Add(new SqlParameter("@FTech", SqlDbType.NVarChar, 50, "FTech"));            cmd.Parameters.Add(new SqlParameter("@FSubject", SqlDbType.NVarChar, 50, "FSubject"));            cmd.Parameters.Add(new SqlParameter("@FJiF", SqlDbType.NVarChar, 100, "FJiF"));            cmd.Parameters.Add(new SqlParameter("@FJobAdd", SqlDbType.NVarChar, 100, "FJobAdd"));            cmd.Parameters.Add(new SqlParameter("@FStartTime", SqlDbType.DateTime, 0, "FStartTime"));            cmd.Parameters.Add(new SqlParameter("@FEndTime", SqlDbType.DateTime, 0, "FEndTime"));            cmd.Parameters["@FName"].Value = "Herbert";            SqlDataReader rdr = cmd.ExecuteReader();            BindingSource bs = new BindingSource();            bs.DataSource = rdr;            this.dataGridView1.DataSource = bs;            conn.Close();  

這裡在dataGridView1裡面就可以顯示返回結果了。