不固定参数的存储过程

简介:

我们知道存储过程是不支持不固定参数情况的(包括数组参数),可是有时候我们的参数又必须是不固定的,怎么办呢?我想此时不妨使用字符串参数来帮助我们解决这种情况,利用字符串分割的方法将一个参数分割成数个参数来解决。下面我们看一个例子:

假设现在给你一个产品信息列表(显示出各个商品的基本信息),现在我想要根据所选择商品进行统计(任意选择几种),例如统计出价格<10,11-20,21-30,31-40,41-50,50以上的商品个有多少个(姑且认为就统计这些)。此时如果使用存储过程就势必需要传入所选商品的id作为参数,但是id个数是不固定的。此时估计会有人这样写:

 

复制代码

    
    
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
--
Author: jianxin160
--
Create date: 2010.11.05
--
Description: 统计商品
--
=============================================
ALTER PROCEDURE StatProductInfo
(
@ids VARCHAR ( 8000 )
)
AS
BEGIN
DECLARE @followingTen INT
DECLARE @elevenToTwenty INT
DECLARE @twentyOneToThirty INT
DECLARE @thirtyOneToFourty INT
DECLARE @fourtyOneToFifty INT
DECLARE @fiftyOrMore INT

SELECT @followingTen = COUNT ( * )
FROM dbo.Products
WHERE ProductID IN ( @ids ) AND UnitPrice < 10

SELECT @elevenToTwenty = COUNT ( * )
FROM dbo.Products
WHERE ProductID IN ( @ids ) AND UnitPrice BETWEEN 11 AND 20

SELECT @twentyOneToThirty = COUNT ( * )
FROM dbo.Products
WHERE ProductID IN ( @ids ) AND UnitPrice BETWEEN 21 AND 30

SELECT @thirtyOneToFourty = COUNT ( * )
FROM dbo.Products
WHERE ProductID IN ( @ids ) AND UnitPrice BETWEEN 31 AND 40

SELECT @fourtyOneToFifty = COUNT ( * )
FROM dbo.Products
WHERE ProductID IN ( @ids ) AND UnitPrice BETWEEN 41 AND 50

SELECT @fiftyOrMore = COUNT ( * )
FROM dbo.Products
WHERE ProductID IN ( @ids ) AND UnitPrice > 50

SELECT @followingTen AS ' <$10 ' , @elevenToTwenty AS ' $11-$20 ' ,
@twentyOneToThirty AS ' $21-$30 ' , @thirtyOneToFourty AS ' $31-$40 ' ,
@fourtyOneToFifty AS ' $41-$50 ' , @fiftyOrMore AS ' >$50 '
END
GO
复制代码

其实如果你测试一下(例如:EXEC dbo . StatProductInfo '3,4,8,10,22')是有问题的,sql server认为这整个是一个参数,转换时出错。此时我们想一下如果这些字段在一个虚表中就容易操作多了,但是一般虚表是有其他表通过查询得到,现在根本无法查询又哪来的虚表呢?聪明的朋友或许已经想到可以使用"表值函数"。对,答案就是使用"表值函数"。我们知道"表值函数"可以返回一个"Table"类型的变量(相当于一张虚表,存放于内存中),我们首先将字符串分割存放到"表值函数"的一个字段中,然后我们再从"表值函数"中查询就可以了(这个例子也是"表值函数"的一个典型应用)。具体sql如下:

复制代码

    
    
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
--
Author: cmj
--
Create date: 2010.11.05
--
Description: 返回一个Table,只有一列,每一行的数据就是分割好的字符串
--
=============================================
CREATE FUNCTION GetSplitFieldsByString
(
@toSplitString varchar ( 1000 ),
@splitChar varchar ( 10 )
)
RETURNS
@tb TABLE (sp varchar ( 100 ))
AS
BEGIN
DECLARE @i INT
SET @toSplitString = RTRIM ( LTRIM ( @toSplitString ))
SET @i = CHARINDEX ( @splitChar , @toSplitString )
WHILE @i > 0
BEGIN
INSERT @tb VALUES ( LEFT ( @toSplitString , @i - 1 ))
SET @toSplitString =RIGHT ( @toSplitString , LEN ( @toSplitString ) - @i )
SET @i = CHARINDEX ( @splitChar , @toSplitString )
END
IF LEN ( @toSplitString ) > 0
INSERT @tb VALUES ( @toSplitString )
RETURN
END
GO
复制代码

然后我们稍微修改一下存储过程:

复制代码

    
    
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
--
Author: jianxin160
--
Create date: 2010.11.05
--
Description: 统计商品
--
=============================================
ALTER PROCEDURE StatProductInfo
(
@ids VARCHAR ( 8000 )
)
AS
BEGIN
DECLARE @followingTen INT
DECLARE @elevenToTwenty INT
DECLARE @twentyOneToThirty INT
DECLARE @thirtyOneToFourty INT
DECLARE @fourtyOneToFifty INT
DECLARE @fiftyOrMore INT

SELECT @followingTen = COUNT ( * )
FROM dbo.Products
WHERE ProductID IN ( SELECT sp FROM dbo.GetSplitFieldsByString( @ids , ' , ' )) AND UnitPrice < 10

SELECT @elevenToTwenty = COUNT ( * )
FROM dbo.Products
WHERE ProductID IN ( SELECT sp FROM dbo.GetSplitFieldsByString( @ids , ' , ' )) AND UnitPrice BETWEEN 11 AND 20

SELECT @twentyOneToThirty = COUNT ( * )
FROM dbo.Products
WHERE ProductID IN ( SELECT sp FROM dbo.GetSplitFieldsByString( @ids , ' , ' )) AND UnitPrice BETWEEN 21 AND 30

SELECT @thirtyOneToFourty = COUNT ( * )
FROM dbo.Products
WHERE ProductID IN ( SELECT sp FROM dbo.GetSplitFieldsByString( @ids , ' , ' )) AND UnitPrice BETWEEN 31 AND 40

SELECT @fourtyOneToFifty = COUNT ( * )
FROM dbo.Products
WHERE ProductID IN ( SELECT sp FROM dbo.GetSplitFieldsByString( @ids , ' , ' )) AND UnitPrice BETWEEN 41 AND 50

SELECT @fiftyOrMore = COUNT ( * )
FROM dbo.Products
WHERE ProductID IN ( SELECT sp FROM dbo.GetSplitFieldsByString( @ids , ' , ' )) AND UnitPrice > 50

SELECT @followingTen AS ' <$10 ' , @elevenToTwenty AS ' $11-$20 ' , @twentyOneToThirty AS ' $21-$30 ' ,
@thirtyOneToFourty AS ' $31-$40 ' , @fourtyOneToFifty AS ' $41-$50 ' , @fiftyOrMore AS ' >$50 '
END
GO
复制代码

这样通过执行EXEC dbo . StatProductInfo '3,4,8,10,22' 就可以得到想要的结果了:

知识共享许可协议
作品采用知识共享署名 2.5 中国大陆许可协议进行许可,欢迎转载,演绎或用于商业目的。但转载请注明来自崔江涛(KenshinCui),并包含相关链接。

知识共享许可协议 本作品采用知识共享署名 2.5 中国大陆许可协议进行许可,欢迎转载,演绎或用于商业目的。但转载请注明来自崔江涛(KenshinCui),并包含相关链接。
目录
相关文章
|
存储
SQLServer存储过程中的输出参数的使用
SQLServer存储过程中的输出参数的使用
61 0
|
6月前
|
存储 关系型数据库 MySQL
MySQL数据库——存储过程-if条件判断、参数、case(介绍、用法、案例)
MySQL数据库——存储过程-if条件判断、参数、case(介绍、用法、案例)
392 0
|
存储 SQL 关系型数据库
MySQL中不同类型参数存储过程示例
MySQL中不同类型参数存储过程示例
150 0
|
存储 SQL 关系型数据库
【MySQL速通篇003】MySQL视图,MySQL触发器,MySQL函数,MySQL存储过程(参数分类,存储过程的增删改查等),SQL的动态执行,支持事务的存储过程,pymysql 2
【MySQL速通篇003】MySQL视图,MySQL触发器,MySQL函数,MySQL存储过程(参数分类,存储过程的增删改查等),SQL的动态执行,支持事务的存储过程,pymysql 2
346 0
|
存储 SQL NoSQL
【MySQL速通篇003】MySQL视图,MySQL触发器,MySQL函数,MySQL存储过程(参数分类,存储过程的增删改查等),SQL的动态执行,支持事务的存储过程,pymysql 1
【MySQL速通篇003】MySQL视图,MySQL触发器,MySQL函数,MySQL存储过程(参数分类,存储过程的增删改查等),SQL的动态执行,支持事务的存储过程,pymysql 1
491 0
|
存储 SQL
sql server 存储过程传递表名参数及Dynamic SQL
sql server 存储过程传递表名参数及Dynamic SQL
sql server 存储过程传递表名参数及Dynamic SQL
|
存储 SQL 关系型数据库
MySQL的存储过程——输入参数(in)、输出参数(out)、输入输出参数(inout)
MySQL的存储过程——输入参数(in)、输出参数(out)、输入输出参数(inout)
2309 0
MySQL的存储过程——输入参数(in)、输出参数(out)、输入输出参数(inout)
|
存储 关系型数据库 MySQL
【MySQL】使用pdo调用存储过程 --带参数输出
【MySQL】使用pdo调用存储过程 --带参数输出
197 0
【MySQL】使用pdo调用存储过程 --带参数输出
|
存储 SQL 数据库连接
SQL SERVER使用ODBC 驱动建立的链接服务器调用存储过程时参数不能为NULL值
原文:SQL SERVER使用ODBC 驱动建立的链接服务器调用存储过程时参数不能为NULL值     我们知道SQL SERVER建立链接服务器(Linked Server)可以选择的驱动程序非常多,最近发现使用ODBC 的 Microsoft OLE DB 驱动程序建立的链接服务器(Linked Server), 调用存储过程过程时,参数不能为NULL值。
891 0
|
存储 MySQL 关系型数据库
MySQL存储过程使用表名做参数
版权声明:本文为博主chszs的原创文章,未经博主允许不得转载。 https://blog.csdn.net/chszs/article/details/5818363 MySQL存储过程使用表名做参数 原创文章,chszs版权所有! 如要转发,请联系chszs! 盗贴行为将受起诉! MySQL存储过程使用表名做参数动态创建表的例子。
787 0