SQL Server Stored Procedures dynamically Add where conditions

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) asdeclare @ strwhere varchar (500) if (@ cdate = NULL) set @ strwhere = @ strwhere + 'and cdate =' + @ cdateif (@ 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 shipping 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 category, pagenum as barcode number, fitting_code as assembly number, line_code as production machine number, flag as release, remark as remarks, other as others, blank columns from t_infocode_natural where 1 = 1' + @ strwhereexec (@ strmain)

 multi-condition query Stored Procedure Example 2 

------------ 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 partition, [fsex] [char] (1) Collate chinese_prc_ci_as not null, [feducation] [char] (1) Collate chinese_prc_ci_as not null, [fcardid] [char] (18) collate partition 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 partition, [fexamnum] [varchar] (30) Collate partition 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_lookgocreate 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 asdeclare @ sqlstr varchar (100) if @ fname is not nullbegin set @ sqlstr = 'where fname = '+ ''' + @ fname + ''' end if @ fnameen is not nullbegin if @ sqlstr is not null set @ sqlstr = @ sqlstr + 'and fnameen =' + ''' + @ fnameen + ''' else set @ sqlstr = 'where fnameen = '+ ''' '+ @ fnameen + ''' end if @ fcardid is not nullbegin if @ sqlstr is not null set @ sqlstr = @ sqlstr +' and fcardid = '+ ''' + @ fcardid + ''' else set @ sqlstr = 'where fcardid = '+ ''' + @ fcardid + ''' endif @ fexamnum is not nullbegin if @ sqlstr is not null set @ sqlstr = @ sqlstr + 'and fexamnum =' + ''' + @ fexamnum + ''' else set @ sqlstr = 'where fexamnum = '+'' ''+ @ fexamnum + ''' endif @ fservernum is not nullbegin if @ sqlstr is not null set @ sqlstr = @ sqlstr + 'and fservernum =' + ''' + @ fservernum + ''' else set @ sqlstr = 'where fservernum = '+ ''' + @ fservernum + ''' endif @ fsex is not nullbegin if @ sqlstr is not null set @ sqlstr = @ sqlstr + 'and fsex =' + ''' + @ fsex + ''' else set @ sqlstr = 'where fsex = '+'' ''' + @ fsex + ''' endif @ feducation is not nullbegin if @ sqlstr is not null set @ sqlstr = @ sqlstr + 'and feducation =' + ''' + @ feducation + ''' else set @ sqlstr = 'where feducation = '+ ''' + @ feducation + ''' end if @ fisae is not nullbegin if @ sqlstr is not null set @ sqlstr = @ sqlstr + 'and fisae =' + ''' + @ fisae + ''' else set @ sqlstr = 'where fisae = '+' ''' + @ fisae + ''' endif @ ftech is not nullbegin if @ sqlstr is not null set @ sqlstr = @ sqlstr + 'and ftech =' + '''' + @ ftech + ''' else set @ sqlstr = 'where ftech = '+ ''' + @ ftech + ''' endif @ fsubject is not null begin if @ sqlstr is not null set @ sqlstr = @ sqlstr + 'and fsubject =' + ''' + @ fsubject + ''' else set @ sqlstr = 'where fsubject = '+ ''' + @ fsubject + ''' endif @ fjif is not nullbegin if @ sqlstr is not null set @ sqlstr = @ sqlstr + 'and fjif =' + ''' '+ @ fjif + ''' else set @ sqlstr = 'where fjif =' + ''' + @ fjif + ''' endif @ fjobadd is not nullbegin if @ sqlstr is not null set @ sqlstr = @ sqlstr + 'and fjobadd =' + ''' + @ fjobadd + ''' else set @ sqlstr = 'where fjobadd = '+ '''' + @ fjobadd + ''' endexec ('select * From stuinfo' + @ sqlstr) go