Multi-condition query Stored Procedure

Multi-condition query Stored Procedure Example 1
Create proc pr_select_natural
(
@ Cdate datetime,
@ Fac_name varchar (50 ),
@ Battery_type varchar (20 ),
@ Make_mode varchar (20 ),
@ Produce_mode varchar (20 ),
@ Year char (4 ),
@ Month char (2 ),
@ Tdate datetime,
@ Pagenum varchar (12)
)
As
Declare @ strwhere varchar (500)
If (@ cdate = NULL)
Set @ strwhere = @ strwhere + 'and cdate =' + @ cdate
If (@ fac_name = NULL)
Set @ strwhere = @ strwhere + 'and fac_name like ''%'' + @ fac_name + ''% '''
If (@ battery_type = NULL)
Set @ strwhere = @ strwhere + 'and battery_type like ''%'' + @ battery_type + ''% '''
If (@ make_mode = NULL)
Set @ strwhere = @ strwhere + 'and make_mode like ''%'' + @ make_mode + ''% '''
If (@ produce_mode = NULL)
Set @ strwhere = @ strwhere + 'and produce_mode like ''%'' + @ produce_mode + ''% '''
If (@ year = NULL)
Set @ strwhere = @ strwhere + 'and year like ''%'' + @ year + ''% '''
If (@ month = NULL)
Set @ strwhere = @ strwhere + 'and month like ''%'' + @ month + ''% '''
If (@ tdate = NULL)
Set @ strwhere = @ strwhere + 'and tdate = @ tdate'
If (@ pagenum = NULL)
Set @ strwhere = @ strwhere + 'and pagenum like ''%'' + @ pagenum + ''% '''

Set @ strmain = 'select
Cdate as factory time,
Fac_name as shipping manufacturer,
Battery_type as battery model,
Send_type as delivery type,
Send_num as detection quantity,
Make_mode as production method,
Produce_mode as production method,
Battery_num as battery pack quantity,
[Year] as year,
[Month] As month,
Tdate as date,
Send_rule as shipping rule,
Send_code as shipping group number,
Battery_sort as battery type,
Pagenum as barcode number,
Fitting_code as assembly number,
Line_code as production host number,
Flag as is allowed,
Remark as remarks,
Other as others,
Blank columns
From t_infocode_natural
Where 1 = 1' + @ strwhere
Exec (@ strmain)

 

Multi-condition query Stored Procedure Example 2

------------ 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 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