我们知道存储过程是不支持不固定参数情况的（包括数组参数），可是有时候我们的参数又必须是不固定的，怎么办呢？我想此时不妨使用字符串参数来帮助我们解决这种情况，利用字符串分割的方法将一个参数分割成数个参数来解决。下面我们看一个例子：
假设现在给你一个产品信息列表（显示出各个商品的基本信息），现在我想要根据所选择商品进行统计（任意选择几种），例如统计出价格<10,11-20,21-30,31-40,41-50,50以上的商品个有多少个（姑且认为就统计这些）。此时如果使用存储过程就势必需要传入所选商品的id作为参数，但是id个数是不固定的。此时估计会有人这样写：
set ansi_nulls on
go
set quoted_identifier on
-- =============================================
-- 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(*)
where productid in(@ids) and unitprice between 11 and 20
select @twentyonetothirty=count(*)
where productid in(@ids) and unitprice between 21 and 30
select @thirtyonetofourty=count(*)
where productid in(@ids) and unitprice between 31 and 40
select @fourtyonetofifty=count(*)
where productid in(@ids) and unitprice between 41 and 50
select @fiftyormore=count(*)
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
其实如果你测试一下（例如：exec dbo . statproductinfo '3,4,8,10,22'）是有问题的，sql server认为这整个是一个参数，转换时出错。此时我们想一下如果这些字段在一个虚表中就容易操作多了，但是一般虚表是有其他表通过查询得到，现在根本无法查询又哪来的虚表呢？聪明的朋友或许已经想到可以使用"表值函数"。对，答案就是使用"表值函数"。我们知道"表值函数"可以返回一个"table"类型的变量（相当于一张虚表，存放于内存中），我们首先将字符串分割存放到"表值函数"的一个字段中，然后我们再从"表值函数"中查询就可以了（这个例子也是"表值函数"的一个典型应用）。具体sql如下：
-- author: cmj
-- description: 返回一个table,只有一列，每一行的数据就是分割好的字符串
create function getsplitfieldsbystring
@tosplitstring varchar(1000),
@splitchar varchar(10)
returns
@tb table(sp varchar(100))
declare @i int
set @tosplitstring=rtrim(ltrim(@tosplitstring))
set @i=charindex(@splitchar,@tosplitstring)
while @i>0
insert @tb values(left(@tosplitstring,@i-1))
set @tosplitstring=right(@tosplitstring,len(@tosplitstring)-@i)
if len(@tosplitstring)>0
insert @tb values(@tosplitstring)
return
然后我们稍微修改一下存储过程：
where productid in(select sp from dbo.getsplitfieldsbystring(@ids,',')) and unitprice<10
where productid in(select sp from dbo.getsplitfieldsbystring(@ids,',')) and unitprice between 11 and 20
where productid in(select sp from dbo.getsplitfieldsbystring(@ids,',')) and unitprice between 21 and 30
where productid in(select sp from dbo.getsplitfieldsbystring(@ids,',')) and unitprice between 31 and 40
where productid in(select sp from dbo.getsplitfieldsbystring(@ids,',')) and unitprice between 41 and 50
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'
这样通过执行exec dbo . statproductinfo '3,4,8,10,22' 就可以得到想要的结果了：