SQL Server multi-condition Query [Stored Procedure] classic example

★First of all, I would like to thank the Netizens [from the SQL server technology space group] for their simple support...
Note the difference between null and ''.
Select * from table where fdate> comparison between '2007-6-7' or between time ..........
------------ Table field ---------------
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,
[Fj'if] [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
---------- Stored Procedure -------------------------
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 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 = 'male'
Set @ wom = 'female'
Set @ is = 'yes'
Set @ No = 'no' if @ fname <>''
Begin
Set @ sqlstr = 'where fname = '+ ''' + @ fname + ''''
End

If @ fnameen <>''
Begin
If @ sqlstr <>''
Set @ sqlstr = @ sqlstr + 'and fnameen =' + ''' + @ fnameen + ''''
Else
Set @ sqlstr = 'where fnameen = '+ ''' + @ fnameen + ''''
End

If @ fcardid <>''
Begin
If @ sqlstr <>''
Set @ sqlstr = @ sqlstr + 'and fcardid =' + ''' + @ fcardid + ''''
Else
Set @ sqlstr = 'where fcardid = '+ ''' + @ fcardid + ''''
End

If @ fexamnum <>''
Begin
If @ sqlstr <>''
Set @ sqlstr = @ sqlstr + 'and fexamnum =' + ''' + @ fexamnum + ''''
Else
Set @ sqlstr = 'where fexamnum = '+ ''' + @ fexamnum + ''''
End


If @ fservernum <>''
Begin
If @ sqlstr <>''
Set @ sqlstr = @ sqlstr + 'and fservernum =' + ''' + @ fservernum + ''''
Else
Set @ sqlstr = 'where fservernum = '+ ''' + @ fservernum + ''''
End if @ fsex <>''
Begin
If @ sqlstr <>''
Set @ sqlstr = @ sqlstr + 'and fsex =' + ''' + @ fsex + ''''
Else
Set @ sqlstr = 'where fsex = '+ ''' + @ fsex + ''''
End if @ feducation <>''
Begin
If @ sqlstr <>''
Set @ sqlstr = @ sqlstr + 'and feducation =' + ''' + @ feducation + ''''

Else
Set @ sqlstr = 'where feducation = '+ ''' + @ feducation + ''''

End if @ fisae <>''
Begin
If @ sqlstr <>''
Set @ sqlstr = @ sqlstr + 'and fisae =' + ''' + @ fisae + ''''

Else
Set @ sqlstr = 'where fisae = '+ ''' + @ fisae + ''''

End if @ ftech <>''
Begin
If @ sqlstr <>''
Set @ sqlstr = @ sqlstr + 'and ftech =' + ''' + @ ftech + ''''
Else
Set @ sqlstr = 'where ftech = '+ ''' + @ ftech + ''''
End

If @ fsubject <>''
Begin
If @ sqlstr <>''
Set @ sqlstr = @ sqlstr + 'and fsubject =' + ''' + @ fsubject + ''''
Else
Set @ sqlstr = 'where fsubject = '+ ''' + @ fsubject + ''''
End

If @ fjif <>''
Begin
If @ sqlstr <>''
Set @ sqlstr = @ sqlstr + 'and fjif =' + ''' + @ fjif + ''''
Else
Set @ sqlstr = 'where fjif = '+ ''' + @ fjif + ''''
End if @ fjobadd <>''
Begin
If @ sqlstr <>''
Set @ sqlstr = @ sqlstr + 'and fjobadd =' + ''' + @ fjobadd + ''''
Else
Set @ sqlstr = 'where fjobadd = '+ ''' + @ fjobadd + ''''
End

If @ fstarttime <>''
Begin

If @ sqlstr <>''

Set @ sqlstr = @ sqlstr + 'and fexamdate> =' + ''' + @ fstarttime + ''' else
Set @ sqlstr = 'where fexamdate> = '+ ''' + @ fstarttime + ''''
End

If @ fendtime <>''
Begin
If @ sqlstr <>''
Set @ sqlstr = @ sqlstr + 'and fexamdate <=' + ''' + @ fendtime + ''''
Else
Set @ sqlstr = 'where fexamdate <= '+ ''' + @ fendtime + ''''
End
Exec ('select fnumber, fname, fnameen, fsex = case when fsex = 0 then '+ ''' + @ man + ''' + 'else' + ''' + @ wom + ''' + 'End, feducation, fcardid, fbirdate, ftech, fsubject, fjif, fjobadd, fexamnum, fservernum, fexamdate, fisae = case when fisae = 0 then '+ ''' + @ is + ''' + 'else' + ''' + @ No + ''' + 'End from stuinfo' + @ sqlstr) go