我們知道存儲過程是不支援不固定參數情況的（包括數組參數），可是有時候我們的參數又必須是不固定的，怎麼辦呢？我想此時不妨使用字元串參數來幫助我們解決這種情況，利用字元串分割的方法将一個參數分割成數個參數來解決。下面我們看一個例子：
假設現在給你一個産品資訊清單（顯示出各個商品的基本資訊），現在我想要根據所選擇商品進行統計（任意選擇幾種），例如統計出價格<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' 就可以得到想要的結果了：