不固定参数的存储过程
我们知道存储过程是不支持不固定参数情况的(包括数组参数),可是有时候我们的参数又必须是不固定的,怎么办呢?我想此时不妨使用字符串参数来帮助我们解决这种情况,利用字符串分割的方法将一个参数分割成数个参数来解决。下面我们看一个例子:
假设现在给你一个产品信息列表(显示出各个商品的基本信息),现在我想要根据所选择商品进行统计(任意选择几种),例如统计出价格<10,11-20,21-30,31-40,41-50,50以上的商品个有多少个(姑且认为就统计这些)。此时如果使用存储过程就势必需要传入所选商品的id作为参数,但是id个数是不固定的。此时估计会有人这样写:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: jianxin160
-- Create date: 2010.11.05
-- Description: 统计商品
-- =============================================
ALTERPROCEDURE StatProductInfo
(
@idsVARCHAR(8000)
)
AS
BEGIN
DECLARE@followingTenINT
DECLARE@elevenToTwentyINT
DECLARE@twentyOneToThirtyINT
DECLARE@thirtyOneToFourtyINT
DECLARE@fourtyOneToFiftyINT
DECLARE@fiftyOrMoreINT
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 BETWEEN11AND20
SELECT@twentyOneToThirty=COUNT(*)
FROM dbo.Products
WHERE ProductID IN(@ids) AND UnitPrice BETWEEN21AND30
SELECT@thirtyOneToFourty=COUNT(*)
FROM dbo.Products
WHERE ProductID IN(@ids) AND UnitPrice BETWEEN31AND40
SELECT@fourtyOneToFifty=COUNT(*)
FROM dbo.Products
WHERE ProductID IN(@ids) AND UnitPrice BETWEEN41AND50
SELECT@fiftyOrMore=COUNT(*)
FROM dbo.Products
WHERE ProductID IN(@ids) AND UnitPrice>50
SELECT@followingTenAS'<$10',@elevenToTwentyAS'$11-$20',
@twentyOneToThirtyAS'$21-$30',@thirtyOneToFourtyAS'$31-$40',
@fourtyOneToFiftyAS'$41-$50',@fiftyOrMoreAS'>$50'
END
GO
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: jianxin160
-- Create date: 2010.11.05
-- Description: 统计商品
-- =============================================
ALTERPROCEDURE StatProductInfo
(
@idsVARCHAR(8000)
)
AS
BEGIN
DECLARE@followingTenINT
DECLARE@elevenToTwentyINT
DECLARE@twentyOneToThirtyINT
DECLARE@thirtyOneToFourtyINT
DECLARE@fourtyOneToFiftyINT
DECLARE@fiftyOrMoreINT
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 BETWEEN11AND20
SELECT@twentyOneToThirty=COUNT(*)
FROM dbo.Products
WHERE ProductID IN(@ids) AND UnitPrice BETWEEN21AND30
SELECT@thirtyOneToFourty=COUNT(*)
FROM dbo.Products
WHERE ProductID IN(@ids) AND UnitPrice BETWEEN31AND40
SELECT@fourtyOneToFifty=COUNT(*)
FROM dbo.Products
WHERE ProductID IN(@ids) AND UnitPrice BETWEEN41AND50
SELECT@fiftyOrMore=COUNT(*)
FROM dbo.Products
WHERE ProductID IN(@ids) AND UnitPrice>50
SELECT@followingTenAS'<$10',@elevenToTwentyAS'$11-$20',
@twentyOneToThirtyAS'$21-$30',@thirtyOneToFourtyAS'$31-$40',
@fourtyOneToFiftyAS'$41-$50',@fiftyOrMoreAS'>$50'
END
GO
其实如果你测试一下(例如:EXEC dbo . StatProductInfo '3,4,8,10,22' )是有问题的,sql server认为这整个是一个参数,转换时出错。此时我们想一下如果这些字段在一个虚表中就容易操作多了,但是一般虚表是有其他表通过查询得到,现在根本无法查询又哪来的虚表呢?聪明的朋友或许已经想到可以使用"表值函数"。对,答案就是使用"表值函数"。我们知道"表值函数"可以返回一个"Table"类型的变量(相当于一张虚表,存放于内存中),我们首先将字符串分割存放到"表值函数"的一个字段中,然后我们再从"表值函数"中查询就可以了(这个例子也是"表值函数"的一个典型应用)。具体sql如下:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: cmj
-- Create date: 2010.11.05
-- Description: 返回一个Table,只有一列,每一行的数据就是分割好的字符串
-- =============================================
CREATEFUNCTION GetSplitFieldsByString
(
@toSplitStringvarchar(1000),
@splitCharvarchar(10)
)
RETURNS
@tbTABLE(sp varchar(100))
AS
BEGIN
DECLARE@iINT
SET@toSplitString=RTRIM(LTRIM(@toSplitString))
SET@i=CHARINDEX(@splitChar,@toSplitString)
WHILE@i>0
BEGIN
INSERT@tbVALUES(LEFT(@toSplitString,@i-1))
SET@toSplitString=RIGHT(@toSplitString,LEN(@toSplitString)-@i)
SET@i=CHARINDEX(@splitChar,@toSplitString)
END
IFLEN(@toSplitString)>0
INSERT@tbVALUES(@toSplitString)
RETURN
END
GO
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: cmj
-- Create date: 2010.11.05
-- Description: 返回一个Table,只有一列,每一行的数据就是分割好的字符串
-- =============================================
CREATEFUNCTION GetSplitFieldsByString
(
@toSplitStringvarchar(1000),
@splitCharvarchar(10)
)
RETURNS
@tbTABLE(sp varchar(100))
AS
BEGIN
DECLARE@iINT
SET@toSplitString=RTRIM(LTRIM(@toSplitString))
SET@i=CHARINDEX(@splitChar,@toSplitString)
WHILE@i>0
BEGIN
INSERT@tbVALUES(LEFT(@toSplitString,@i-1))
SET@toSplitString=RIGHT(@toSplitString,LEN(@toSplitString)-@i)
SET@i=CHARINDEX(@splitChar,@toSplitString)
END
IFLEN(@toSplitString)>0
INSERT@tbVALUES(@toSplitString)
RETURN
END
GO
然后我们稍微修改一下存储过程:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: jianxin160
-- Create date: 2010.11.05
-- Description: 统计商品
-- =============================================
ALTERPROCEDURE StatProductInfo
(
@idsVARCHAR(8000)
)
AS
BEGIN
DECLARE@followingTenINT
DECLARE@elevenToTwentyINT
DECLARE@twentyOneToThirtyINT
DECLARE@thirtyOneToFourtyINT
DECLARE@fourtyOneToFiftyINT
DECLARE@fiftyOrMoreINT
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 BETWEEN11AND20
SELECT@twentyOneToThirty=COUNT(*)
FROM dbo.Products
WHERE ProductID IN(SELECT sp FROM dbo.GetSplitFieldsByString(@ids,',')) AND UnitPrice BETWEEN21AND30
SELECT@thirtyOneToFourty=COUNT(*)
FROM dbo.Products
WHERE ProductID IN(SELECT sp FROM dbo.GetSplitFieldsByString(@ids,',')) AND UnitPrice BETWEEN31AND40
SELECT@fourtyOneToFifty=COUNT(*)
FROM dbo.Products
WHERE ProductID IN(SELECT sp FROM dbo.GetSplitFieldsByString(@ids,',')) AND UnitPrice BETWEEN41AND50
SELECT@fiftyOrMore=COUNT(*)
FROM dbo.Products
WHERE ProductID IN(SELECT sp FROM dbo.GetSplitFieldsByString(@ids,',')) AND UnitPrice>50
SELECT@followingTenAS'<$10',@elevenToTwentyAS'$11-$20',@twentyOneToThirtyAS'$21-$30',
@thirtyOneToFourtyAS'$31-$40',@fourtyOneToFiftyAS'$41-$50',@fiftyOrMoreAS'>$50'
END
GO
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: jianxin160
-- Create date: 2010.11.05
-- Description: 统计商品
-- =============================================
ALTERPROCEDURE StatProductInfo
(
@idsVARCHAR(8000)
)
AS
BEGIN
DECLARE@followingTenINT
DECLARE@elevenToTwentyINT
DECLARE@twentyOneToThirtyINT
DECLARE@thirtyOneToFourtyINT
DECLARE@fourtyOneToFiftyINT
DECLARE@fiftyOrMoreINT
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 BETWEEN11AND20
SELECT@twentyOneToThirty=COUNT(*)
FROM dbo.Products
WHERE ProductID IN(SELECT sp FROM dbo.GetSplitFieldsByString(@ids,',')) AND UnitPrice BETWEEN21AND30
SELECT@thirtyOneToFourty=COUNT(*)
FROM dbo.Products
WHERE ProductID IN(SELECT sp FROM dbo.GetSplitFieldsByString(@ids,',')) AND UnitPrice BETWEEN31AND40
SELECT@fourtyOneToFifty=COUNT(*)
FROM dbo.Products
WHERE ProductID IN(SELECT sp FROM dbo.GetSplitFieldsByString(@ids,',')) AND UnitPrice BETWEEN41AND50
SELECT@fiftyOrMore=COUNT(*)
FROM dbo.Products
WHERE ProductID IN(SELECT sp FROM dbo.GetSplitFieldsByString(@ids,',')) AND UnitPrice>50
SELECT@followingTenAS'<$10',@elevenToTwentyAS'$11-$20',@twentyOneToThirtyAS'$21-$30',
@thirtyOneToFourtyAS'$31-$40',@fourtyOneToFiftyAS'$41-$50',@fiftyOrMoreAS'>$50'
END
GO
这样通过执行EXEC dbo . StatProductInfo '3,4,8,10,22' 就可以得到想要的结果了:
本作品采用知识共享署名 2.5 中国大陆许可协议进行许可,欢迎转载,演绎或用于商业目的。但转载请注明来自崔江涛(KenshinCui),并包含相关链接。
![]() |
本作品采用知识共享署名 2.5 中国大陆许可协议进行许可,欢迎转载,演绎或用于商业目的。但转载请注明来自崔江涛(KenshinCui),并包含相关链接。 |