多条件查询存储过程代码,MSSQL代码库,德仔网

代码语言
代码分类
【MSSQL】多条件查询存储过程
作者:Dezai.CN / 发布于2012/7/31/ 724
多条件查询存储过程
<div>------------表中的字段---------------
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_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</div>
试试其它关键字
　多条件查询存储过程　
同语言下
可能有用的
Dezai.CN贡献的其它代码(4037)