不固定参数的存储过程实现代码
网络编程 2021-07-05 13:42www.168986.cn编程入门
我们知道存储过程是不支持不固定参数情况的（包括数组参数），可是有时候我们的参数又必须是不固定的，怎么办呢？
我想此时不妨使用字符串参数来帮助我们解决这种情况，利用字符串分割的方法将一个参数分割成数个参数来解决。狼蚁网站SEO优化我们看一个例子
假设现在给你一个产品信息列表（显示出各个商品的基本信息），现在我想要根据所选择商品进行统计（任意选择几种），例如统计出价格<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' 就可以得到想要的结果了
试试这样会不会快一些
SELECT SUM(CASE WHEN UnitPrice < 10 THEN 1 ELSE 0 END) '<$10',
SUM(CASE WHEN UnitPrice BETWEEN 11 AND 20 THEN 1 ELSE 0 END) '$11-$20',
SUM(CASE WHEN UnitPrice BETWEEN 21 AND 30 THEN 1 ELSE 0 END) '$21-$30',
...
SUM(CASE WHEN UnitPrice > 50 THEN 1 ELSE 0 END) '>$10'
FROM dbo.Products
WHERE ProductID IN(SELECT sp FROM dbo.GetSplitFieldsByString(@ids,','))
假设现在给你一个产品信息列表（显示出各个商品的基本信息），现在我想要根据所选择商品进行统计（任意选择几种），例如统计出价格<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' 就可以得到想要的结果了
试试这样会不会快一些
代码如下:
SELECT SUM(CASE WHEN UnitPrice < 10 THEN 1 ELSE 0 END) '<$10',
SUM(CASE WHEN UnitPrice BETWEEN 11 AND 20 THEN 1 ELSE 0 END) '$11-$20',
SUM(CASE WHEN UnitPrice BETWEEN 21 AND 30 THEN 1 ELSE 0 END) '$21-$30',
...
SUM(CASE WHEN UnitPrice > 50 THEN 1 ELSE 0 END) '>$10'
FROM dbo.Products
WHERE ProductID IN(SELECT sp FROM dbo.GetSplitFieldsByString(@ids,','))
上一篇：MSSQL2000安全设置图文教程
下一篇：不同数据库之间导入导出功能介绍
编程语言
- 如何快速学会编程 如何快速学会ug编程
- 免费学编程的app 推荐12个免费学编程的好网站
- 电脑怎么编程：电脑怎么编程网咯游戏菜单图标
- 如何写代码新手教学 如何写代码新手教学手机
- 基础编程入门教程视频 基础编程入门教程视频华
- 编程演示：编程演示浦丰投针过程
- 乐高编程加盟 乐高积木编程加盟
- 跟我学plc编程 plc编程自学入门视频教程
- ug编程成航林总 ug编程实战视频
- 孩子学编程的好处和坏处
- 初学者学编程该从哪里开始 新手学编程从哪里入
- 慢走丝编程 慢走丝编程难学吗
- 国内十强少儿编程机构 中国少儿编程机构十强有
- 成人计算机速成培训班 成人计算机速成培训班办
- 孩子学编程网上课程哪家好 儿童学编程比较好的
- 代码编程教学入门软件 代码编程教程