主页 > 知识库 > 数据库 > 数据库综合 >

解析不固定参数的存储过程

来源:TechTarget中国 作者:网络 发表于:2012-07-06 10:30  点击:
我们知道存储过程是不支持不固定参数情况的(包括数组参数),可是有时候我们的参数又必须是不固定的,怎么办呢?我想此时不妨使用字符串参数来帮助我们解决这种情况,利用字符串分割的方法将一个参数分割成数个参数来解决。下面我们看一个例子: 假设现在给你
我们知道存储过程是不支持不固定参数情况的(包括数组参数),可是有时候我们的参数又必须是不固定的,怎么办呢?我想此时不妨使用字符串参数来帮助我们解决这种情况,利用字符串分割的方法将一个参数分割成数个参数来解决。下面我们看一个例子:
   假设现在给你一个产品信息列表(显示出各个商品的基本信息),现在我想要根据所选择商品进行统计(任意选择几种),例如统计出价 格<10,11-20,21-30,31-40,41-50,50以上的商品个有多少个(姑且认为就统计这些)。此时如果使用存储过程就势必需要传 入所选商品的id作为参数,但是id个数是不固定的。此时估计会有人这样写:


双击代码全选
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
  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如下:




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



双击代码全选
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
  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' 就可以得到想要的结果了:

    有帮助
    (0)
    0%
    没帮助
    (0)
    0%