• 售前

  • 售后

热门帖子
入门百科

不固定参数的存储过程实当代码

[复制链接]
不好吃荤漳 显示全部楼层 发表于 2021-10-26 13:04:34 |阅读模式 打印 上一主题 下一主题
我想此时不妨使用字符串参数来资助我们解决这种环境,使用字符串分割的方法将一个参数分割成数个参数来解决。下面我们看一个例子:

假设现在给你一个产品信息列表(表现出各个商品的根本信息),现在我想要根据所选择商品举行统计(恣意选择几种),比方统计出价格<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,','))

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?立即注册

x

帖子地址: 

回复

使用道具 举报

分享
推广
火星云矿 | 预约S19Pro,享500抵1000!
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

草根技术分享(草根吧)是全球知名中文IT技术交流平台,创建于2021年,包含原创博客、精品问答、职业培训、技术社区、资源下载等产品服务,提供原创、优质、完整内容的专业IT技术开发社区。
  • 官方手机版

  • 微信公众号

  • 商务合作