不固定參數的存儲進程完成代碼。本站提示廣大學習愛好者：（不固定參數的存儲進程完成代碼）文章只能為提供參考，不一定能成為您想要的結果。以下是不固定參數的存儲進程完成代碼正文
我想此時無妨應用字符串參數來贊助我們處理這類情形，應用字符串朋分的辦法將一個參數朋分成數個參數來處理。上面我們看一個例子：
假定如今給你一個產物信息列表（顯示出各個商品的根本信息），如今我想要依據所選擇商品停止統計（隨意率性選擇幾種），例如統計出價錢<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,','))
