我们知道存储过程是不支持不固定参数情况的（包括数组参数），可是有时候我们的参数又必须是不固定的，怎么办呢？我想此时不妨使用字符串参数来帮助我们解决这种情况，利用字符串分割的方法将一个参数分割成数个参数来解决。下面我们看一个例子：
假设现在给你一个产品信息列表（显示出各个商品的基本信息），现在我想要根据所选择商品进行统计（任意选择几种），例如统计出价格<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' 就可以得到想要的结果了：
本作品采用知识共享署名 2.5 中国大陆许可协议进行许可，欢迎转载，演绎或用于商业目的。但转载请注明来自崔江涛（KenshinCui），并包含相关链接。
|
本作品采用知识共享署名 2.5 中国大陆许可协议进行许可，欢迎转载，演绎或用于商业目的。但转载请注明来自崔江涛（KenshinCui），并包含相关链接。 |
