Sql Server 預存程序 動態添加WHERE條件

多條件查詢預存程序 例一

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 出廠時間,  FAC_NAME  as 發貨廠家,  BATTERY_TYPE as 電池型號,  SEND_TYPE as 發貨類型,  SEND_NUM as 檢測數量,  MAKE_MODE as 製作方式,  PRODUCE_MODE as 生產方式,  BATTERY_NUM as 電池組數量,  [YEAR]  as 年,  [MONTH] as 月,  TDATE  as 日期,  SEND_RULE as 發貨規則,  SEND_CODE as 發貨組編號,  BATTERY_SORT as 電池類別,  PAGENUM as 條碼號,  FITTING_CODE as 裝配號,  LINE_CODE as 生產機號,  FLAG  as 是否允許存取,  REMARK  as 備忘,  OTHER  as 其他,  BLANK_COL as 空白列    from T_INFOCODE_NATURAL where 1=1 ' +@strWhereexec(@strMAIN) 

 多條件查詢預存程序 例二

------------表中的欄位---------------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_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