不固定參數(shù)的存儲過程實(shí)現(xiàn)代碼
發(fā)布日期：2022-01-27 17:51 | 文章來源：gibhub
假設(shè)現(xiàn)在給你一個產(chǎn)品信息列表（顯示出各個商品的基本信息），現(xiàn)在我想要根據(jù)所選擇商品進(jìn)行統(tǒng)計(jì)（任意選擇幾種），例如統(tǒng)計(jì)出價(jià)格<10,11-20,21-30,31-40,41-50,50以上的商品個有多少個（姑且認(rèn)為就統(tǒng)計(jì)這些）。此時如果使用存儲過程就勢必需要傳入所選商品的id作為參數(shù)，但是id個數(shù)是不固定的。此時估計(jì)會有人這樣寫：
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: jianxin160
-- Create date: 2010.11.05
-- Description: 統(tǒng)計(jì)商品
-- =============================================
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
其實(shí)如果你測試一下（例如：EXEC dbo . StatProductInfo '3,4,8,10,22' ）是有問題的，sql server認(rèn)為這整個是一個參數(shù)，轉(zhuǎn)換時出錯。此時我們想一下如果這些字段在一個虛表中就容易操作多了，但是一般虛表是有其他表通過查詢得到，現(xiàn)在根本無法查詢又哪來的虛表呢？聰明的朋友或許已經(jīng)想到可以使用"表值函數(shù)"。對，答案就是使用"表值函數(shù)"。我們知道"表值函數(shù)"可以返回一個"Table"類型的變量（相當(dāng)于一張?zhí)摫?，存放于?nèi)存中），我們首先將字符串分割存放到"表值函數(shù)"的一個字段中，然后我們再從"表值函數(shù)"中查詢就可以了（這個例子也是"表值函數(shù)"的一個典型應(yīng)用）。具體sql如下：
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: cmj
-- Create date: 2010.11.05
-- Description: 返回一個Table,只有一列，每一行的數(shù)據(jù)就是分割好的字符串
-- =============================================
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: 統(tǒng)計(jì)商品
-- =============================================
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
這樣通過執(zhí)行EXEC dbo . StatProductInfo '3,4,8,10,22' 就可以得到想要的結(jié)果了：
試試這樣會不會快一些
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,','))
復(fù)制代碼 代碼如下:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: jianxin160
-- Create date: 2010.11.05
-- Description: 統(tǒng)計(jì)商品
-- =============================================
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
其實(shí)如果你測試一下（例如：EXEC dbo . StatProductInfo '3,4,8,10,22' ）是有問題的，sql server認(rèn)為這整個是一個參數(shù)，轉(zhuǎn)換時出錯。此時我們想一下如果這些字段在一個虛表中就容易操作多了，但是一般虛表是有其他表通過查詢得到，現(xiàn)在根本無法查詢又哪來的虛表呢？聰明的朋友或許已經(jīng)想到可以使用"表值函數(shù)"。對，答案就是使用"表值函數(shù)"。我們知道"表值函數(shù)"可以返回一個"Table"類型的變量（相當(dāng)于一張?zhí)摫?，存放于?nèi)存中），我們首先將字符串分割存放到"表值函數(shù)"的一個字段中，然后我們再從"表值函數(shù)"中查詢就可以了（這個例子也是"表值函數(shù)"的一個典型應(yīng)用）。具體sql如下：
復(fù)制代碼 代碼如下:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: cmj
-- Create date: 2010.11.05
-- Description: 返回一個Table,只有一列，每一行的數(shù)據(jù)就是分割好的字符串
-- =============================================
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
然后我們稍微修改一下存儲過程：
復(fù)制代碼 代碼如下:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: jianxin160
-- Create date: 2010.11.05
-- Description: 統(tǒng)計(jì)商品
-- =============================================
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
這樣通過執(zhí)行EXEC dbo . StatProductInfo '3,4,8,10,22' 就可以得到想要的結(jié)果了：
試試這樣會不會快一些
復(fù)制代碼 代碼如下:
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,','))
版權(quán)聲明：本站文章來源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請保持原文完整并注明來源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非www.sddonglingsh.com所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來，僅供學(xué)習(xí)參考，不代表本站立場，如有內(nèi)容涉嫌侵權(quán)，請聯(lián)系alex-e#qq.com處理。
相關(guān)文章
關(guān)注官方微信