(轉)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

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 NVARCHAR(20)=null,

@FIsAE CHAR(1)=null,

@FTech NVARCHAR(50)=null,

@FSubject NVARCHAR(50)=null,

@FJiF NVARCHAR(100)=null,

@FJobAdd NVARCHAR(100)=null,

@FStartTime VARCHAR(20)=null,

@FEndTime VARCHAR(20)=null

AS

declare @sqlStr varchar(500)

        declare @man char(2)

        declare @wom char(2)

        declare @is char(2)

        declare @no char(2)

        set @man ='男'

        set @wom ='女'

        set @is ='是'

        set @no ='否' if @FName <>''

begin

end

if @FNameen <>''

    if @sqlStr <>''

    else

if @FCardID <>''

if @sqlStr <>''

else

if @FExamNum <>''

if @FServerNum <>''

   if @sqlStr <>''

   else

end if @FSex <>''

end if @FEducation <>''

                else

end if @FIsAE <>''

end if @FTech <>''

if @FSubject <>''

   begin

   end

if @FJiF <>''

end if @FJobAdd <>''

if @FStartTime <>''

                begin

if @FEndTime <>''

   end