不固定参数的存储过程

我们知道存储过程是不支持不固定参数的情况的（包括数组参数），可是有时候我们的参数又必须是不固定的，怎么办呢？我想我们不妨使用字符串参数来帮助我们解决这种情况，利用字符串分割的方法将一个参数分割成数个参数来解决。下面我们看一个例子：

假设现在给你一个产品信息列表（显示出各个商品的基本信息），现在我想要根据所选择商品进行统计（任意选择几种），例如统计出价格<10,11-20,21-30,31-40,41-50,50以上的商品个有多少个（姑且认为就统计这些）。此时如果使用存储过程就势必需要传入所选商品的id作为参数，但是id个数是不固定的。此时估计会有人这样写：

SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author:jianxin160-- Create date: 2010.11.05-- Description:统计商品-- =============================================ALTER PROCEDURE StatProductInfo(@ids VARCHAR(8000))ASBEGINDECLARE @followingTen INTDECLARE @elevenToTwenty INTDECLARE @twentyOneToThirty INTDECLARE @thirtyOneToFourty INTDECLARE @fourtyOneToFifty INTDECLARE @fiftyOrMore INTSELECT @followingTen=COUNT(*)FROM dbo.Products WHERE ProductID IN(@ids) AND UnitPrice<10SELECT @elevenToTwenty=COUNT(*)FROM dbo.Products WHERE ProductID IN(@ids) AND UnitPrice BETWEEN 11 AND 20SELECT @twentyOneToThirty=COUNT(*)FROM dbo.Products WHERE ProductID IN(@ids) AND UnitPrice BETWEEN 21 AND 30SELECT @thirtyOneToFourty=COUNT(*)FROM dbo.Products WHERE ProductID IN(@ids) AND UnitPrice BETWEEN 31 AND 40SELECT @fourtyOneToFifty=COUNT(*)FROM dbo.Products WHERE ProductID IN(@ids) AND UnitPrice BETWEEN 41 AND 50SELECT @fiftyOrMore=COUNT(*)FROM dbo.Products WHERE ProductID IN(@ids) AND UnitPrice>50SELECT @followingTen AS '<$10',@elevenToTwenty AS '$11-$20',@twentyOneToThirty AS '$21-$30',@thirtyOneToFourty AS '$31-$40',@fourtyOneToFifty AS '$41-$50',@fiftyOrMore AS '>$50'ENDGO

其实如果你测试一下（例如：EXECdbo.StatProductInfo'3,4,8,10,22'）是有问题的，sql server认为这整个是一个参数，转换时出错。此时我们想一下如果这写字段在一个虚表中就容易操作多了，但是一般虚表是有其他表通过查询得到，现在根本无法查询又哪来的虚表呢？聪明的朋友或许已经想到可以使用"表值函数"，对，答案就是使用"表值函数"。我们知道"表值函数"可以返回一个"Table"类型的变量（相当于一张虚表，存放于内存中），我们首先将字符串分割存放到"表值函数"的一个字段中，然后我们再"表值函数"中查询就可以了（这个例子也是"表值函数"的一个经典应用）。具体sql如下：

SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author:cmj-- Create date: 2010.11.05-- Description:返回一个Table,只有一列，每一行的数据就是分割好的字符串-- =============================================CREATE FUNCTION GetSplitFieldsByString(@toSplitString varchar(1000),@splitChar varchar(10))RETURNS @tb TABLE(sp varchar(100))ASBEGINDECLARE @i INTSET @toSplitString=RTRIM(LTRIM(@toSplitString))SET @i=CHARINDEX(@splitChar,@toSplitString)WHILE @i>0BEGININSERT @tb VALUES(LEFT(@toSplitString,@i-1))SET @toSplitString=RIGHT(@toSplitString,LEN(@toSplitString)-@i)SET @i=CHARINDEX(@splitChar,@toSplitString)ENDIF LEN(@toSplitString)>0INSERT @tb VALUES(@toSplitString)RETURN ENDGO

然后我们稍微修改一下存储过程：

SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author:jianxin160-- Create date: 2010.11.05-- Description:统计商品-- =============================================ALTER PROCEDURE StatProductInfo(@ids VARCHAR(8000))ASBEGINDECLARE @followingTen INTDECLARE @elevenToTwenty INTDECLARE @twentyOneToThirty INTDECLARE @thirtyOneToFourty INTDECLARE @fourtyOneToFifty INTDECLARE @fiftyOrMore INTSELECT @followingTen=COUNT(*)FROM dbo.Products WHERE ProductID IN(SELECT sp FROM dbo.GetSplitFieldsByString(@ids,',')) AND UnitPrice<10SELECT @elevenToTwenty=COUNT(*)FROM dbo.Products WHERE ProductID IN(SELECT sp FROM dbo.GetSplitFieldsByString(@ids,',')) AND UnitPrice BETWEEN 11 AND 20SELECT @twentyOneToThirty=COUNT(*)FROM dbo.Products WHERE ProductID IN(SELECT sp FROM dbo.GetSplitFieldsByString(@ids,',')) AND UnitPrice BETWEEN 21 AND 30SELECT @thirtyOneToFourty=COUNT(*)FROM dbo.Products WHERE ProductID IN(SELECT sp FROM dbo.GetSplitFieldsByString(@ids,',')) AND UnitPrice BETWEEN 31 AND 40SELECT @fourtyOneToFifty=COUNT(*)FROM dbo.Products WHERE ProductID IN(SELECT sp FROM dbo.GetSplitFieldsByString(@ids,',')) AND UnitPrice BETWEEN 41 AND 50SELECT @fiftyOrMore=COUNT(*)FROM dbo.Products WHERE ProductID IN(SELECT sp FROM dbo.GetSplitFieldsByString(@ids,',')) AND UnitPrice>50SELECT @followingTen AS '<$10',@elevenToTwenty AS '$11-$20',@twentyOneToThirty AS '$21-$30',@thirtyOneToFourty AS '$31-$40',@fourtyOneToFifty AS '$41-$50',@fiftyOrMore AS '>$50'ENDGO

这样通过执行EXECdbo.StatProductInfo'3,4,8,10,22'就可以得到想要的结果了：