SqlServer 多条件查询 [存储过程] 经典例子
来源:互联网 发布:spf算法 编辑:程序博客网 时间:2024/05/22 04:38
首先要感谢 网友[来自Sql Server 技术空间群]简单爱的大力支持...
需要注意的问题 NULL, ' ' 的区别
SELECT *FROM 表 WHERE Fdate > ' 2007-6-7' 字符之间的比较,还是时间之间的比较..........
------------表中的字段---------------
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 NVARCHAR(20)=null,
@FIsAE CHAR(1)=null,
@FTech NVARCHAR(50)=null,
@FSubject NVARCHAR(50)=null,
@FJiF NVARCHAR(100)=null,
@FJobAdd NVARCHAR(100)=null,
@FStartTime VARCHAR(20)=null,
@FEndTime VARCHAR(20)=null
AS
declare @sqlStr varchar(500)
declare @man char(2)
declare @wom char(2)
declare @is char(2)
declare @no char(2)
set @man ='男'
set @wom ='女'
set @is ='是'
set @no ='否' if @FName <>''
begin
set @sqlStr=' where FName='+''''+@FName+''''
end
if @FNameen <>''
begin
if @sqlStr <>''
set @sqlStr=@sqlStr+' and FNameen='+''''+@FNameen+''''
else
set @sqlStr=' where FNameen='+''''+@FNameen+''''
end
if @FCardID <>''
begin
if @sqlStr <>''
set @sqlStr=@sqlStr+' and FCardID='+''''+@FCardID+''''
else
set @sqlStr=' where FCardID='+''''+@FCardID+''''
end
if @FExamNum <>''
begin
if @sqlStr <>''
set @sqlStr=@sqlStr+' and FExamNum='+''''+@FExamNum+''''
else
set @sqlStr=' where FExamNum='+''''+@FExamNum +''''
end
if @FServerNum <>''
begin
if @sqlStr <>''
set @sqlStr=@sqlStr+' and FServerNum='+''''+@FServerNum+''''
else
set @sqlStr=' where FServerNum='+''''+@FServerNum+''''
end if @FSex <>''
begin
if @sqlStr <>''
set @sqlStr=@sqlStr+' and FSex='+''''+@FSex+''''
else
set @sqlStr=' where FSex='+''''+@FSex+''''
end if @FEducation <>''
begin
if @sqlStr <>''
set @sqlStr=@sqlStr+' and FEducation='+''''+@FEducation+''''
else
set @sqlStr=' where FEducation='+''''+@FEducation+''''
end if @FIsAE <>''
begin
if @sqlStr <>''
set @sqlStr=@sqlStr+' and FIsAE= '+''''+@FIsAE+''''
else
set @sqlStr=' where FIsAE= '+''''+@FIsAE+''''
end if @FTech <>''
begin
if @sqlStr <>''
set @sqlStr=@sqlStr+' and FTech='+''''+@FTech+''''
else
set @sqlStr=' where FTech='+''''+@FTech+''''
end
if @FSubject <>''
begin
if @sqlStr <>''
set @sqlStr=@sqlStr+' and FSubject='+''''+@FSubject+''''
else
set @sqlStr=' where FSubject='+''''+@FSubject +''''
end
if @FJiF <>''
begin
if @sqlStr <>''
set @sqlStr=@sqlStr+' and FJiF='+''''+@FJiF+''''
else
set @sqlStr=' where FJiF='+''''+@FJiF+''''
end if @FJobAdd <>''
begin
if @sqlStr <>''
set @sqlStr=@sqlStr+' and FJobAdd='+''''+@FJobAdd +''''
else
set @sqlStr=' where FJobAdd='+''''+@FJobAdd +''''
end
if @FStartTime <>''
begin
if @sqlStr <>''
set @sqlStr=@sqlStr+' and FExamDate>= '+''''+@FStartTime+'''' else
set @sqlStr=' where FExamDate>= '+''''+@FStartTime+''''
end
if @FEndTime <>''
begin
if @sqlStr <>''
set @sqlStr=@sqlStr+' and FExamDate<= '+''''+@FEndTime+''''
else
set @sqlStr=' where FExamDate<= '+''''+@FEndTime+''''
end
EXEC('select FNumber,FName,FNameen,FSex=CASE WHEN FSex=0 THEN ' +''''+@man+''''+ ' ELSE '+''''+ @wom+''''+ ' END,FEducation,FCardID,FBirDate,FTech,FSubject,FJiF,FJobAdd,FExamNum,FServerNum,FExamDate,FIsAE=CASE WHEN FIsAE=0 THEN'+ ''''+@is+''''+' ELSE '+''''+@no+''''+' END from stuInfo'+@sqlStr)GO
需要注意的问题 NULL, ' ' 的区别
SELECT *FROM 表 WHERE Fdate > ' 2007-6-7' 字符之间的比较,还是时间之间的比较..........
------------表中的字段---------------
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 NVARCHAR(20)=null,
@FIsAE CHAR(1)=null,
@FTech NVARCHAR(50)=null,
@FSubject NVARCHAR(50)=null,
@FJiF NVARCHAR(100)=null,
@FJobAdd NVARCHAR(100)=null,
@FStartTime VARCHAR(20)=null,
@FEndTime VARCHAR(20)=null
AS
declare @sqlStr varchar(500)
declare @man char(2)
declare @wom char(2)
declare @is char(2)
declare @no char(2)
set @man ='男'
set @wom ='女'
set @is ='是'
set @no ='否' if @FName <>''
begin
set @sqlStr=' where FName='+''''+@FName+''''
end
if @FNameen <>''
begin
if @sqlStr <>''
set @sqlStr=@sqlStr+' and FNameen='+''''+@FNameen+''''
else
set @sqlStr=' where FNameen='+''''+@FNameen+''''
end
if @FCardID <>''
begin
if @sqlStr <>''
set @sqlStr=@sqlStr+' and FCardID='+''''+@FCardID+''''
else
set @sqlStr=' where FCardID='+''''+@FCardID+''''
end
if @FExamNum <>''
begin
if @sqlStr <>''
set @sqlStr=@sqlStr+' and FExamNum='+''''+@FExamNum+''''
else
set @sqlStr=' where FExamNum='+''''+@FExamNum +''''
end
if @FServerNum <>''
begin
if @sqlStr <>''
set @sqlStr=@sqlStr+' and FServerNum='+''''+@FServerNum+''''
else
set @sqlStr=' where FServerNum='+''''+@FServerNum+''''
end if @FSex <>''
begin
if @sqlStr <>''
set @sqlStr=@sqlStr+' and FSex='+''''+@FSex+''''
else
set @sqlStr=' where FSex='+''''+@FSex+''''
end if @FEducation <>''
begin
if @sqlStr <>''
set @sqlStr=@sqlStr+' and FEducation='+''''+@FEducation+''''
else
set @sqlStr=' where FEducation='+''''+@FEducation+''''
end if @FIsAE <>''
begin
if @sqlStr <>''
set @sqlStr=@sqlStr+' and FIsAE= '+''''+@FIsAE+''''
else
set @sqlStr=' where FIsAE= '+''''+@FIsAE+''''
end if @FTech <>''
begin
if @sqlStr <>''
set @sqlStr=@sqlStr+' and FTech='+''''+@FTech+''''
else
set @sqlStr=' where FTech='+''''+@FTech+''''
end
if @FSubject <>''
begin
if @sqlStr <>''
set @sqlStr=@sqlStr+' and FSubject='+''''+@FSubject+''''
else
set @sqlStr=' where FSubject='+''''+@FSubject +''''
end
if @FJiF <>''
begin
if @sqlStr <>''
set @sqlStr=@sqlStr+' and FJiF='+''''+@FJiF+''''
else
set @sqlStr=' where FJiF='+''''+@FJiF+''''
end if @FJobAdd <>''
begin
if @sqlStr <>''
set @sqlStr=@sqlStr+' and FJobAdd='+''''+@FJobAdd +''''
else
set @sqlStr=' where FJobAdd='+''''+@FJobAdd +''''
end
if @FStartTime <>''
begin
if @sqlStr <>''
set @sqlStr=@sqlStr+' and FExamDate>= '+''''+@FStartTime+'''' else
set @sqlStr=' where FExamDate>= '+''''+@FStartTime+''''
end
if @FEndTime <>''
begin
if @sqlStr <>''
set @sqlStr=@sqlStr+' and FExamDate<= '+''''+@FEndTime+''''
else
set @sqlStr=' where FExamDate<= '+''''+@FEndTime+''''
end
EXEC('select FNumber,FName,FNameen,FSex=CASE WHEN FSex=0 THEN ' +''''+@man+''''+ ' ELSE '+''''+ @wom+''''+ ' END,FEducation,FCardID,FBirDate,FTech,FSubject,FJiF,FJobAdd,FExamNum,FServerNum,FExamDate,FIsAE=CASE WHEN FIsAE=0 THEN'+ ''''+@is+''''+' ELSE '+''''+@no+''''+' END from stuInfo'+@sqlStr)GO
- SqlServer 多条件查询 [存储过程] 经典例子
- SqlServer 多条件查询 [存储过程] 经典例子
- 存储过程多条件查询
- sqlserver 存储过程例子
- SqlServer分页存储过程(多表查询,多条件排序),Repeater控件呈现数据以及分页
- SQLServer 存储过程中不拼接SQL字符串实现多条件查询
- SQLServer 存储过程中不拼接SQL字符串实现多条件查询
- SQLServer 存储过程中不拼接SQL字符串实现多条件查询
- SQLServer 存储过程中不拼接SQL字符串实现多条件查询
- SQLServer 存储过程中不拼接SQL字符串实现多条件查询
- 存储过程条件查询
- 经典存储过程例子
- 存储过程-分隔符号-多条件查询
- 多条件查询存储过程 例一
- 多条件查询存储过程-SQL Server
- sqlserver2005多选,多条件查询存储过程
- Oracle多条件查询分页存储过程
- SQLSERVER 2000 存储过程例子
- NET 下数据库图片的存入与读取
- 工作总结
- TRAC使用说明
- DataGridView 实际项目当中需要注意的问题
- ASP.NET面试题(参考,大部分实用)
- SqlServer 多条件查询 [存储过程] 经典例子
- AJAX框架 jMaki (二) – 鱼眼(FishEye)实例
- GridView隐藏列取值解决方案
- Hopes Dim for Measures to Conserve Energy
- C# 企业级开发案例 :
- mistake
- Java精华积累:初学者都应该搞懂的问题
- California Officials Tackle a Toothy Lake Predator
- ComboBox 经验总结[动态绑定数据之后,静态添加一条数据]