SQL Server multi-condition Query [Stored Procedure] classic example combined with C # usage

Fields in the table:

 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

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 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 @ SqlstrVarchar (100) If @ Fname Is   Not   Null  Begin    Set @ Sqlstr = 'Where fname =' + ''  '' + @ Fname + ''  ''   End          If @ Fnameen Is   Not   Null  Begin      If @ SqlstrIs   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 

You can run this stored procedure in the SQL Server Query analyzer and track @ sqlstr

ExecProc_look Herbert
 

The followingCodeDemonstrate how to use the above stored procedure in 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 ();

The returned results are displayed in datagridview1.