【原创源码】(02)：通用查询存储过程，可应用于多表关联的动态条件查询。

【原创源码】(02)：通用查询存储过程，可应用于多表关联的动态条件查询。

欢迎大家发表意见（漏洞，性能等）。在博客园社区以外转载，请注明作者和出处。谢谢！

1，创建数据表：

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Demo]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[Demo]

CREATE TABLE [dbo].[Demo] (

[DemoId] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,

[DemoName] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,

[ListPrice] [decimal](18, 0) NULL ,

[Quantity] [int] NULL ,

[LastUpdatedDate] [datetime] NULL

) ON [PRIMARY]

ALTER TABLE [dbo].[Demo] WITH NOCHECK ADD

CONSTRAINT [PK_ApplePie] PRIMARY KEY CLUSTERED

(

[DemoId]

) ON [PRIMARY]

2，创建存储过程：

CREATE procedure usp_selectDemo

@DemoId varchar(50) = null,

@DemoName varchar(50) = null,

@ListPrice decimal = null,

@Quantity int = null,

@LastUpdatedDate datetime = null,

@LastUpdatedDateBegin datetime = null,

@LastUpdatedDateEnd datetime = null

--select * from demo

--usp_selectDemo '1'

/* Powered by taeheelive@hotmail.com

declare @sql varchar(500)

set @sql = ' select DemoId, DemoName, ListPrice, Quantity, LastUpdatedDate from Demo where 1=1'

if @DemoId is not null

begin set @sql = @sql + ' AND DemoId = '''+@DemoId+'''' end

if @DemoName is not null

begin set @sql = @sql + ' AND DemoName = '''+@DemoName+'''' end

if @ListPrice is not null

begin set @sql = @sql + ' AND ListPrice = '+convert(varchar(10),@ListPrice)+'' end

if @Quantity is not null

begin set @sql = @sql + ' AND Quantity = '+convert(varchar(10),@Quantity)+'' end

if @LastUpdatedDate is not null

begin set @sql = @sql + ' AND LastUpdatedDate = '''+convert(varchar(10),@LastUpdatedDate,120)++''' ' end

if @LastUpdatedDateBegin is not null

begin set @sql = @sql + ' AND LastUpdatedDate >= '''+convert(varchar(10),@LastUpdatedDateBegin,120)++''' ' end

if @LastUpdatedDateEnd is not null

begin set @sql = @sql + ' AND LastUpdatedDate < '''+convert(varchar(10),@LastUpdatedDateEnd,120)+''' ' end

--print (@sql)

exec (@sql)

/* Powered by 江千帆(cnblogs.com) */

SELECT DemoId, DemoName, ListPrice, Quantity, LastUpdatedDate FROM Demo

where 1=1

and (@DemoId is null or DemoId = @DemoId)

and (@DemoName is null or DemoName = @DemoName)

and (@ListPrice is null or ListPrice = @ListPrice)

and (@Quantity is null or Quantity = @Quantity)

and (@LastUpdatedDate is null or LastUpdatedDate = @LastUpdatedDate)

and (@LastUpdatedDateBegin is null or LastUpdatedDate >= @LastUpdatedDateBegin)

and (@LastUpdatedDateEnd is null or LastUpdatedDate < @LastUpdatedDateEnd)

3，查询方法：

查询所有记录：usp_selectDemo
查询DemoId='1'的记录：usp_selectDemo '1'
查询DemoName='maxdemo'的记录：usp_selectDemo null,'maxdemo'
等等。

4，Sorry~! 临时修改了5处Bug
change '+@ListPrice+''   to mailto:'+@ListPrice+''
change mailto:'+@ListPrice+''   to   mailto:'+@ListPrice+''
change '''+@LastUpdatedDate+'''   to   mailto:'+@ListPrice+''
change '''+@LastUpdatedDateBegin+'''   to   mailto:'+@ListPrice+''
change '''+@LastUpdatedDateEnd+'''   to   mailto:'+@ListPrice+''
汗~~,类型转换写的好差，不知哪位高手愿意补完美。谢谢！
ps：为什么上面的字都是当成蓝色的email了，不晓得怎么去掉蓝色和底线，郁闷中。
2006-10-18

5，个人感觉是比较好的一个思路，特大胆放到首页，希望大家讨论哈，优缺点。谢谢！
灵感来自于master数据库中的存储过程“sp_tables”
2006-10-19

6，添加一段数据访问层的应用代码SQLServerDAL，PetShop4.0结构

/// <summary>
2

/// Query for Demo s by condition.
3

/// </summary>
4

/// <remarks>
5

/// 查询多条记录（IList范型存储）。
6

/// </remarks>
7

/// <param name="demoModel">A Demo model.</param>
8

/// <returns>Interface to model Demo collection generic by condition.</returns>
9

public IList<DemoModel> QueryCollection(DemoModel demoModel)
10

{
11

IList<DemoModel> DemoModelCollection = new List<DemoModel>();
12

try
14

{
15

StringBuilder strSql = new StringBuilder();
16

strSql.Append("usp_selectDemo");
17

SqlParameter[] parameters = new SqlParameter[7];
19

parameters[0] = new SqlParameter("@DemoId", SqlDbType.VarChar, 50);
21

if (demoModel.DemoId != null && demoModel.DemoId.Length > 0)
22

parameters[0].Value = demoModel.DemoId;
23

else
24

parameters[0].Value = null;
25

parameters[1] = new SqlParameter("@DemoName", SqlDbType.VarChar, 50);
27

if (demoModel.DemoName != null && demoModel.DemoName.Length > 0)
28

parameters[1].Value = demoModel.DemoName;
29

else
30

parameters[1].Value = null;
31

parameters[2] = new SqlParameter("@ListPrice", SqlDbType.Decimal, 8);
33

if (demoModel.ListPrice != 0)
34

parameters[2].Value = demoModel.ListPrice;
35

else
36

parameters[2].Value = null;
37

parameters[3] = new SqlParameter("@Quantity", SqlDbType.Int, 4);
39

if (demoModel.Quantity != 0)
40

parameters[3].Value = demoModel.Quantity;
41

else
42

parameters[3].Value = null;
43

parameters[4] = new SqlParameter("@LastUpdatedDate", SqlDbType.DateTime, 8);
45

if (demoModel.LastUpdatedDate != DateTime.Parse("1900-1-1 0:00:00"))
46

parameters[4].Value = demoModel.LastUpdatedDate;
47

else
48

parameters[4].Value = null;
49

parameters[5] = new SqlParameter("@LastUpdatedDateBegin", SqlDbType.DateTime, 8);
51

if (demoModel.LastUpdatedDateBegin != DateTime.Parse("1900-1-1 0:00:00"))/*[t]*/
52

parameters[5].Value = demoModel.LastUpdatedDateBegin;
53

else
54

parameters[5].Value = null;
55

parameters[6] = new SqlParameter("@LastUpdatedDateEnd", SqlDbType.DateTime, 8);
57

if (demoModel.LastUpdatedDateEnd != DateTime.Parse("1900-1-1 0:00:00"))/*[t]*/
58

parameters[6].Value = demoModel.LastUpdatedDateEnd;
59

else
60

parameters[6].Value = null;
61

//foreach (DataRow dr in SqlHelper.Query(strSql.ToString()).Tables[0].Rows)
65

//{
66

// DemoModel demo = new DemoModel(dr[0].ToString(), dr[1].ToString(), Convert.ToDecimal(dr[2]), Int32.Parse(dr[3].ToString()), DateTime.Parse(dr[4].ToString()));
67

// DemoModelCollection.Add(demo);
68

//}
69

//Execute a query to read the products
71

using (SqlDataReader rdr = SqlHelper.ExecuteReader(strSql.ToString(), parameters))
72

{
73

while (rdr.Read())
74

{
75

DemoModel demo = new DemoModel();
76

if (!rdr.IsDBNull(0))
78

demo.DemoId = rdr.GetString(0);
79

if (!rdr.IsDBNull(1))
81

demo.DemoName = rdr.GetString(1);
82

if (!rdr.IsDBNull(2))
84

demo.ListPrice = rdr.GetDecimal(2);
85

if (!rdr.IsDBNull(3))
87

demo.Quantity = rdr.GetInt32(3);
88

if (!rdr.IsDBNull(4))
90

demo.LastUpdatedDate = rdr.GetDateTime(4);
91

//DemoModel demo = new DemoModel(rdr.GetString(0), rdr.GetString(1), rdr.GetDecimal(2), rdr.GetInt32(3), rdr.GetDateTime(4));
93

DemoModelCollection.Add(demo);
94

}
95

}
96

return DemoModelCollection;
98

}
99

catch (Exception ex)
100

{
101

throw new Exception(ex.Message, ex);
102

}
103

}

2006-10-19

7，存储过程添加了/* Powered by 江千帆(cnblogs.com) */的方法（默认方法），
因为感觉比我的要优秀,感谢江千帆的意见！2006-10-19

注意！在博客园社区以外转载，必须注明：
作者：Clark Chan
和原文出处：http://clarkchan.cnblogs.com/
否则谢绝转载！

posted on 2006-10-18 15:27 Clark Chan 阅读(1594) 评论(15) 编辑收藏网摘所属分类: 【原创源码】

#1楼 2006-10-18 18:21 高海东

不错
回复引用查看

#2楼 2006-10-19 09:57 daydayup [未注册用户]

汗... 这样的文章
还这么特意的注明转载请注明出处

回复引用

#3楼 [楼主] 2006-10-19 10:28 无赖.net

@daydayup
因为是原创的源代码，虽然挺简单的！！（料到可能被拍砖，所以昨天仅在新手区贴了，今天转到首页希望大家给点意见。）
但思想来自于个人，非抄袭的想法，
如果仅仅转载文章，非学习方法，难道不该尊重原创吗？
也许我的理解没你高吧~~现在的我认为通过自己的思想得到的东西是可以贴上那句话的。

最初是视图的通用查询，后来发觉性能很低，于是想到能用别的方法吗？
试试存储过程，存储过程能避免视图的2次查询，提高速度，然后偶遇“系统的存储过程sp_tables”，就写出了此方法。
回复引用查看

#4楼 2006-10-19 11:09 嘻哈呵嘿

nice topic...
回复引用查看

#5楼 2006-10-19 11:30 江千帆 [未注册用户]

另外一种方法，呵呵
select * from mytable where (column1=@parameter1 or @parameter1 is null)
回复引用

#6楼 2006-10-19 11:40 DELL网站 [未注册用户]

好文章，学习中
回复引用

#7楼 [楼主] 2006-10-19 13:11 无赖.net

@江千帆
不错啊！确实又一种方法！非常感谢指教~！
仔细试验了哈，成功！你的方法比我的优秀，避免了类型转换！！

ps：我把你的方法也贴上，供大家参考，默认你的方法。
只是作了如下修改：
Change (column1=@parameter1 or @parameter1 is null)
to (@parameter1 is null or column1=@parameter1)
感觉会增加速度（个人想像SQLServer 和 C#程序一样，遇到第一个条件为真，将不再判断第二个条件。仅仅在参数不为空的时候，判断第二个条件。）

回复引用查看

#8楼 2006-10-23 11:06 生米煮成稀饭

晕，这有什么好放首页的啊……
回复引用查看

#9楼 2006-10-25 09:28 冯文元[匿名] [未注册用户]

还行啊
回复引用

#10楼 2006-10-29 14:41 chenguangli [未注册用户]

CREATE PROCEDURE dbo.GetKcBMList
(
@SRow NVarChar(30),
@SKey NVarChar(50),
@pagesize int,
@pageindex int,
@docount bit)
as
set nocount on

declare @strFilter NVarChar(1000)
declare @PageLowerBound int
declare @PageUpperBound int
set @PageLowerBound=(@pageindex-1)*@pagesize
set @PageUpperBound=@PageLowerBound+@pagesize

set @strFilter=''
if(@SKey<>'' and @SRow<>'')
set @strFilter=@strFilter+' where ('+@SRow+' like ''%'+@SKey+'%'' )'

--SELECT TOP 1000 CID, TUname, UTel, UWord, KcTitle, KcID, CDate, CoName, CoID FROM KcBM
if(@docount=1)
exec('select count(CID) from KcBM '+@strFilter)
else
begin
exec('
declare @indextable table(id int identity(1,1),nid int)
set rowcount '+@PageUpperBound+'
insert into @indextable(nid) select CID from KcBM '+@strFilter+' order by cid desc
select O.* from KcBM O,@indextable t where O.CID=t.nid and t.id>'+@PageLowerBound+' and t.id<='+@PageUpperBound)
end
set nocount off
GO

回复引用

#11楼 2008-07-24 10:43 mikel

改了一下存储过程，让其支持模糊查询字符类型的列值：

SET @DemoName = '%'+RTRIM(@DemoName) + '%';

SELECT DemoId, DemoName, ListPrice, Quantity, LastUpdatedDate FROM Demo
where 1=1
and (@DemoId is null or DemoId = @DemoId)
and (@DemoName is null or DemoName Like @DemoName)
and (@ListPrice is null or ListPrice = @ListPrice)
and (@Quantity is null or Quantity = @Quantity)
and (@LastUpdatedDate is null or LastUpdatedDate = @LastUpdatedDate)
and (@LastUpdatedDateBegin is null or LastUpdatedDate >= @LastUpdatedDateBegin)
and (@LastUpdatedDateEnd is null or LastUpdatedDate < @LastUpdatedDateEnd)

回复引用查看

#12楼 [楼主] 2008-09-08 18:26 Clark Chan

@mikel
Good

回复引用查看

#13楼 2008-09-09 08:10 mikel

又修改了一下，加入了分页查询：
CREATE PROCEDURE [dbo].[Tang_AreaSelect]
@PageSize int=0,
@CurrentPage int=1,
@Identifier int=NULL,
@AreaName nvarchar(50)=NULL
AS
BEGIN
SET NOCOUNT ON;
IF (NOT @AreaName IS NULL) SET @AreaName='%'+@AreaName+'%'

IF (@PageSize>0)
BEGIN
DECLARE @TotalPage int
SELECT @TotalPage=Count(Identifier) FROM Tang_Area WHERE(@Identifier IS NULL OR Identifier=@Identifier)AND
(@AreaName IS NULL OR AreaName Like @AreaName)
IF(@TotalPage%@PageSize=0)
BEGIN
SET @TotalPage=@TotalPage/@PageSize
END
ELSE
BEGIN
SET @TotalPage=Round(@TotalPage/@PageSize,0)+1
END
SELECT TOP (@PageSize) Identifier,AreaName,@TotalPage as totalPage FROM Tang_Area WHERE Identifier NOT IN (SELECT Top (@PageSize*(@CurrentPage-1))Identifier FROM Tang_Area WHERE (@Identifier IS NULL OR Identifier=@Identifier)AND
(@AreaName IS NULL OR AreaName Like @AreaName))AND(@Identifier IS NULL OR Identifier=@Identifier)AND
(@AreaName IS NULL OR AreaName Like @AreaName)
END
ELSE
BEGIN
SELECT Identifier,AreaName FROM Tang_Area WHERE (@Identifier IS NULL OR Identifier=@Identifier)AND
(@AreaName IS NULL OR AreaName Like @AreaName)
END
END

回复引用查看