SqlServer 多条件查询 [存储过程] 经典例子

★在写这篇文章之前 首先要再次感谢 网友[来自Sql Server 技术空间群]简单爱的大力支持...
 
------------表中的字段---------------
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_Look
GO
CREATE 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
 
  
AS
 declare @sqlStr varchar(100)
       
 if @FName IS NOT NULL
 begin
   set @sqlStr=' where FName='+''''+@FName+''''
 end
       
 if @FNameen IS NOT NULL
 begin
    if @sqlStr IS NOT NULL
        set @sqlStr=@sqlStr+' and FNameen='+''''+@FNameen+''''
    else
   set @sqlStr=' where FNameen='+''''+@FNameen+''''
 end
    
 if @FCardID IS NOT NULL
 begin
  if @sqlStr IS NOT NULL
   set @sqlStr=@sqlStr+' and FCardID='+''''+@FCardID+''''
  else
   set @sqlStr=' where FCardID='+''''+@FCardID+''''
 end
 
 if @FExamNum IS NOT NULL
 begin
  if @sqlStr IS NOT NULL
   set @sqlStr=@sqlStr+' and FExamNum='+''''+@FExamNum+''''
  else
   set @sqlStr=' where FExamNum='+''''+@FExamNum +''''
 end
 
 if @FServerNum IS NOT NULL
 begin
  if @sqlStr IS NOT NULL
   set @sqlStr=@sqlStr+' and FServerNum='+''''+@FServerNum+''''
  else
   set @sqlStr=' where FServerNum='+''''+@FServerNum+''''
 end
 
 if @FSex IS NOT NULL
 begin
  if @sqlStr IS NOT NULL
   set @sqlStr=@sqlStr+' and FSex='+''''+@FSex+''''
  else
   set @sqlStr=' where FSex='+''''+@FSex+''''
 end
 
 if @FEducation IS NOT NULL
 begin
  if @sqlStr IS NOT NULL
   set @sqlStr=@sqlStr+' and FEducation='+''''+@FEducation+''''
                       
                       
  else
   set @sqlStr=' where FEducation='+''''+@FEducation+''''
   
 end

 if @FIsAE IS NOT NULL
 begin
  if @sqlStr IS NOT NULL
   set @sqlStr=@sqlStr+' and FIsAE='+''''+@FIsAE+''''
                       
  else
   set @sqlStr=' where FIsAE='+''''+@FIsAE+''''
                       
 end

 if @FTech IS NOT NULL
 begin
  if @sqlStr IS NOT NULL
   set @sqlStr=@sqlStr+' and FTech='+''''+@FTech+''''
  else
   set @sqlStr=' where FTech='+''''+@FTech+''''
 end
 
 if @FSubject IS NOT NULL
  begin
   if @sqlStr IS NOT NULL
    set @sqlStr=@sqlStr+' and FSubject='+''''+@FSubject+''''
   else
    set @sqlStr=' where FSubject='+''''+@FSubject +''''
  end

 if @FJiF IS NOT NULL
 begin
  if @sqlStr IS NOT NULL
   set @sqlStr=@sqlStr+' and FJiF='+''''+@FJiF+''''
  else
   set @sqlStr=' where FJiF='+''''+@FJiF+''''
 end

 if @FJobAdd IS NOT NULL
 begin
  if @sqlStr IS NOT NULL
   set @sqlStr=@sqlStr+' and FJobAdd='+''''+@FJobAdd +''''
  else
   set @sqlStr=' where FJobAdd='+''''+@FJobAdd +''''
 end

 EXEC('select *from stuInfo '+ @sqlStr)
 GO

转载： http://www.cnblogs.com/hjs1573/archive/2007/06/13/782381.html