Sql Server 存储过程中查询数据无法使用 Union(All)-阿里云开发者社区

　　微软Sql Server数据库中，书写存储过程时，关于查询数据，无法使用Union(All)关联多个查询。

1、先看一段正常的SQL语句，使用了Union(All)查询：

SELECT ci.CustId --客户编号
       ,
       ci.CustNam --客户名称
       ,
       ci.ContactBy --联系人
       ,
       ci.Conacts --联系电话
       ,
       ci.Addr -- 联系地址
       ,
       ci.Notes --备注信息
       ,
       ai2.AreaNam --区域名称,省份名称
       ,
       ISNULL(cc.CType, '')       AS CType--合同类型
       ,
       ISNULL(caat.ArTotal, 0.0)  AS ArTotal --截止到当月底，云想系统账欠款余额
FROM   CustInfo                   AS ci
       INNER  JOIN AreaInfo       AS ai
            ON  ci.AreaCode = ai.AreaCode
       INNER JOIN AreaInfo        AS ai2
            ON  ai.PareaCode = ai2.AreaCode
       LEFT JOIN CustContract     AS cc
            ON  cc.CustId = ci.CustId
       LEFT JOIN CustArApTotal    AS caat
            ON  ci.CustId = caat.CustId
WHERE  ci.CustCatagory = 1
                       
UNION ALL 
                       
SELECT ci.CustId --客户编号
       ,
       ci.CustNam --客户名称
       ,
       ci.ContactBy --联系人
       ,
       ci.Conacts --联系电话
       ,
       ci.Addr -- 联系地址
       ,
       ci.Notes --备注信息
       ,
       ai2.AreaNam --区域名称,省份名称
       ,
       ISNULL(cc.CType, '')     AS CType--合同类型
       ,
       ISNULL(caat.ArTotal, 0)  AS ArTotal --截止到当月底，云想系统账欠款余额
FROM   CustInfo                 AS ci
       INNER  JOIN AreaInfo     AS ai
            ON  ci.AreaCode = ai.AreaCode
       INNER JOIN AreaInfo      AS ai2
            ON  ai.PareaCode = ai2.AreaCode
       INNER JOIN CustContract  AS cc
            ON  cc.CustId = ci.CustId
       LEFT JOIN CustArApTotal  AS caat
            ON  ci.CustId = caat.CustId
WHERE  ci.CustCatagory = 2

View Code

运行结果：查询出441条数据，其中Union(all) 之前的sql语句查询结果为101条记录；

Union(all) 之后的sql语句查询结果为330条记录。

2、创建视图，将以上SQL查询语句放在视图中：

 1 ALTER VIEW [dbo].[VGetCustRelatedInfo2]
 2 AS 
 3 
 4 SELECT ci.CustId --客户编号
 5        ,
 6        ci.CustNam --客户名称
 7        ,
 8        ci.ContactBy --联系人
 9        ,
10        ci.Conacts --联系电话
11        ,
12        ci.Addr -- 联系地址
13        ,
14        ci.Notes --备注信息
15        ,
16        ai2.AreaNam --区域名称,省份名称
17        ,
18        ISNULL(cc.CType, '')       AS CType--合同类型
19        ,
20        ISNULL(caat.ArTotal, 0.0)  AS ArTotal --截止到当月底，云想系统账欠款余额
21 FROM   CustInfo                   AS ci
22        INNER  JOIN AreaInfo       AS ai
23             ON  ci.AreaCode = ai.AreaCode
24        INNER JOIN AreaInfo        AS ai2
25             ON  ai.PareaCode = ai2.AreaCode
26        LEFT JOIN CustContract     AS cc
27             ON  cc.CustId = ci.CustId
28        LEFT JOIN CustArApTotal    AS caat
29             ON  ci.CustId = caat.CustId
30 WHERE  ci.CustCatagory = 1
31                        
32                        UNION ALL 
33                        
34 SELECT ci.CustId --客户编号
35        ,
36        ci.CustNam --客户名称
37        ,
38        ci.ContactBy --联系人
39        ,
40        ci.Conacts --联系电话
41        ,
42        ci.Addr -- 联系地址
43        ,
44        ci.Notes --备注信息
45        ,
46        ai2.AreaNam --区域名称,省份名称
47        ,
48        ISNULL(cc.CType, '')     AS CType--合同类型
49        ,
50        ISNULL(caat.ArTotal, 0)  AS ArTotal --截止到当月底，云想系统账欠款余额
51 FROM   CustInfo                 AS ci
52        INNER  JOIN AreaInfo     AS ai
53             ON  ci.AreaCode = ai.AreaCode
54        INNER JOIN AreaInfo      AS ai2
55             ON  ai.PareaCode = ai2.AreaCode
56        INNER JOIN CustContract  AS cc
57             ON  cc.CustId = ci.CustId
58        LEFT JOIN CustArApTotal  AS caat
59             ON  ci.CustId = caat.CustId
60 WHERE  ci.CustCatagory = 2
61                   
62                
63                
64 
65 
66 GO

View Code

调用视图，运行结果：查询出441条数据，其中Union(all) 之前的sql语句查询结果为101条记录；

Union(all) 之后的sql语句查询结果为330条记录。

3、创建存储过程，代码如下：

  1 /************************************************************
  2  * Code formatted by SoftTree SQL Assistant ?v6.5.258
  3  * Time: 2014/9/12 16:41:46
  4  ************************************************************/
  5 
  6 GO
  7 
  8 /****** Object:  StoredProcedure [dbo].[SP_GetCustRelatedInfo2]    Script Date: 09/12/2014 
  9 
 10 15:48:17 ******/
 11 SET ANSI_NULLS ON
 12 GO
 13 
 14 SET QUOTED_IDENTIFIER ON
 15 GO
 16 
 17 
 18 
 19 -- =============================================
 20 -- Author:      XXX
 21 -- Create date: XXX
 22 -- Description: XXX
 23 -- =============================================
 24 ALTER PROCEDURE [dbo].[SP_GetCustRelatedInfo2]
 25     @custId NVARCHAR(30) --客户编号
 26      ,
 27     @custNam NVARCHAR(1000) --客户名称
 28      ,
 29     @areaNam NVARCHAR(30)--区域、省份名称
 30      ,
 31     @pageSize INT --单页记录条数
 32      ,
 33     @pageIndex INT --当前页左索引    
 34      ,
 35     @totalRowCount INT OUTPUT --输出总记录条数
 36 AS
 37 BEGIN
 38     SET NOCOUNT ON;
 39     
 40     DECLARE @RowStart INT; --定义分页起始位置
 41     DECLARE @RowEnd INT; --定义分页结束位置
 42     
 43     DECLARE @Sql NVARCHAR(MAX); --拼接SQL语句
 44     DECLARE @SqlSelectResult NVARCHAR(MAX); --Sql查询结果语句     
 45     DECLARE @SqlCount NVARCHAR(MAX); --Sql Count计数语句
 46     
 47     IF @pageIndex > 0
 48     BEGIN
 49         SET @pageIndex = @pageIndex -1;
 50         SET @RowStart = @pageSize * @pageIndex + 1;
 51         SET @RowEnd = @RowStart + @pageSize - 1;
 52     END
 53     ELSE
 54     BEGIN
 55         SET @RowStart = 1;
 56         SET @RowEnd = 999999;
 57     END
 58     
 59     IF ISNULL(@pageSize, 0) <> 0
 60     BEGIN
 61         SET @sql = 
 62             'With CTE_CustRelatedInfo as (
 63                 SELECT  ROW_NUMBER () OVER (ORDER BY t.CustId ASC)  AS RowNumber, t.*
 64              FROM   (
 65                  SELECT ci.CustId --客户编号
 66                ,
 67                ci.CustNam --客户名称
 68                ,
 69                ci.ContactBy --联系人
 70                ,
 71                ci.Conacts --联系电话
 72                ,
 73                ci.Addr -- 联系地址
 74                ,
 75                ci.Notes --备注信息
 76                ,
 77                ai2.AreaNam --区域名称,省份名称
 78                ,
 79                ISNULL(cc.CType, '')       AS CType--合同类型
 80                ,
 81                ISNULL(caat.ArTotal, 0.0)  AS ArTotal --截止到当月底，云想系统账欠款余额
 82         FROM   CustInfo                   AS ci
 83                INNER  JOIN AreaInfo       AS ai
 84                     ON  ci.AreaCode = ai.AreaCode
 85                INNER JOIN AreaInfo        AS ai2
 86                     ON  ai.PareaCode = ai2.AreaCode
 87                LEFT JOIN CustContract     AS cc
 88                     ON  cc.CustId = ci.CustId
 89                LEFT JOIN CustArApTotal    AS caat
 90                     ON  ci.CustId = caat.CustId
 91         WHERE  ci.CustCatagory = 1
 92                                
 93     UNION ALL 
 94                                
 95         SELECT ci.CustId --客户编号
 96                ,
 97                ci.CustNam --客户名称
 98                ,
 99                ci.ContactBy --联系人
100                ,
101                ci.Conacts --联系电话
102                ,
103                ci.Addr -- 联系地址
104                ,
105                ci.Notes --备注信息
106                ,
107                ai2.AreaNam --区域名称,省份名称
108                ,
109                ISNULL(cc.CType, '')     AS CType--合同类型
110                ,
111                ISNULL(caat.ArTotal, 0)  AS ArTotal --截止到当月底，云想系统账欠款余额
112         FROM   CustInfo                 AS ci
113                INNER  JOIN AreaInfo     AS ai
114                     ON  ci.AreaCode = ai.AreaCode
115                INNER JOIN AreaInfo      AS ai2
116                     ON  ai.PareaCode = ai2.AreaCode
117                INNER JOIN CustContract  AS cc
118                     ON  cc.CustId = ci.CustId
119                LEFT JOIN CustArApTotal  AS caat
120                     ON  ci.CustId = caat.CustId
121         WHERE  ci.CustCatagory = 2
122                  )
123               AS t 
124                 WHERE 1=1 ';--此处CTE表达式右括号不写，在后面根据条件判断，追加
125     END
126     ELSE
127     BEGIN
128         SET @sql = 
129             'SELECT t.*
130              FROM  (
131              SELECT ci.CustId --客户编号               
132                ,ci.CustNam --客户名称
133                ,
134                ci.ContactBy --联系人
135                ,
136                ci.Conacts --联系电话
137                ,
138                ci.Addr -- 联系地址
139                ,
140                ci.Notes --备注信息
141                ,
142                ai2.AreaNam --区域名称,省份名称
143                ,
144                ISNULL(cc.CType, '')       AS CType--合同类型
145                ,
146                ISNULL(caat.ArTotal, 0.0)  AS ArTotal --截止到当月底，云想系统账欠款余额
147         FROM   CustInfo                   AS ci
148                INNER  JOIN AreaInfo       AS ai
149                     ON  ci.AreaCode = ai.AreaCode
150                INNER JOIN AreaInfo        AS ai2
151                     ON  ai.PareaCode = ai2.AreaCode
152                LEFT JOIN CustContract     AS cc
153                     ON  cc.CustId = ci.CustId
154                LEFT JOIN CustArApTotal    AS caat
155                     ON  ci.CustId = caat.CustId
156         WHERE  ci.CustCatagory = 1
157                                
158         UNION ALL 
159                                
160         SELECT ci.CustId --客户编号
161                ,
162                ci.CustNam --客户名称
163                ,
164                ci.ContactBy --联系人
165                ,
166                ci.Conacts --联系电话
167                ,
168                ci.Addr -- 联系地址
169                ,
170                ci.Notes --备注信息
171                ,
172                ai2.AreaNam --区域名称,省份名称
173                ,
174                ISNULL(cc.CType, '')     AS CType--合同类型
175                ,
176                ISNULL(caat.ArTotal, 0)  AS ArTotal --截止到当月底，云想系统账欠款余额
177         FROM   CustInfo                 AS ci
178                INNER  JOIN AreaInfo     AS ai
179                     ON  ci.AreaCode = ai.AreaCode
180                INNER JOIN AreaInfo      AS ai2
181                     ON  ai.PareaCode = ai2.AreaCode
182                INNER JOIN CustContract  AS cc
183                     ON  cc.CustId = ci.CustId
184                LEFT JOIN CustArApTotal  AS caat
185                     ON  ci.CustId = caat.CustId
186         WHERE  ci.CustCatagory = 2
187                  )
188               AS t
189              WHERE 1=1 ';
190     END
191     
192     IF ISNULL(@custId, '') <> ''
193     BEGIN
194         --根据客户id查询
195         SET @Sql = @Sql + ' AND t.CustId like ''%' + @custId + '%''';
196     END
197     
198     IF ISNULL(@custNam, '') <> ''
199     BEGIN
200         --根据客户名称 模糊查询
201         SET @Sql = @Sql + ' AND t.CustNam like ''%' + @custNam + '%''';
202     END
203     
204     IF ISNULL(@areaNam, '') <> ''
205     BEGIN
206         --根据区域、省份名称
207         SET @Sql = @Sql + ' AND t.AreaNam like ''%' + @areaNam + '%''';
208     END
209     
210     IF ISNULL(@pageSize, 0) <> 0
211     BEGIN
212         SET @Sql = @Sql + ') ';
213         
214         SET @SqlCount = @Sql +
215             ' SELECT @Temp = COUNT(*) FROM CTE_CustRelatedInfo;';
216         
217         SET @SqlSelectResult = @Sql +
218             ' SELECT * FROM CTE_CustRelatedInfo 
219               WHERE RowNumber Between ' + CONVERT(VARCHAR(10), @RowStart) 
220             +
221             ' And ' + CONVERT(VARCHAR(10), @RowEnd) + ';';
222         
223         PRINT (@SqlSelectResult);--打印输出sql语句
224         
225         EXEC sp_executesql @SqlSelectResult;--执行sql查询
226         
227         EXEC sp_executesql @SqlCount,
228              N'@Temp int output',
229              @totalRowCount OUTPUT ; --执行count统计
230     END
231     ELSE
232     BEGIN
233         SET @Sql = @sql + ' order by t.CustId ASC ';
234         SET @totalRowCount = 0; --总记录数
235         PRINT (@Sql);--打印输出sql语句
236         EXEC (@Sql);----打印输出sql语句
237     END
238     
239     SET NOCOUNT OFF;
240 END
241 GO

View Code

　　调用存储过程：

　　DECLARE @totalRowCount INT
　　EXEC SP_GetCustRelatedInfo2 '','','',10000,1,@totalRowCount OUT

运行结果：查询出330条记录。

以上结果说明：Sql Server 存储过程中查询语句无法直接使用 Union(All)。使用之后，程序不报错，但是查询结果会丢失Union(All)之前的所有查询记录，只保留最后一个Union(All)之后查询语句的查询结果记录。

解决方法：

方案1：先创建视图，将使用Union(All)关键字的sql查询语句放在视图中，然后再存储过程中调用视图。如下：

  1 USE [BPMIS_TEST]
  2 GO
  3 
  4 /****** Object:  StoredProcedure [dbo].[SP_GetCustRelatedInfo2]    Script Date: 09/12/2014 15:48:17 ******/
  5 SET ANSI_NULLS ON
  6 GO
  7 
  8 SET QUOTED_IDENTIFIER ON
  9 GO
 10 
 11 
 12 
 13 -- =============================================
 14 -- Author:        张传宁
 15 -- Create date: 2014-9-11
 16 -- Description:    获取对账单评估明细表信息列表
 17 -- =============================================
 18 ALTER PROCEDURE [dbo].[SP_GetCustRelatedInfo2]
 19     @custId NVARCHAR(30) --客户编号
 20      ,
 21     @custNam NVARCHAR(1000) --客户名称
 22      ,
 23     @areaNam NVARCHAR(30)--区域、省份名称
 24      ,
 25     @pageSize INT --单页记录条数
 26      ,
 27     @pageIndex INT --当前页左索引    
 28      ,
 29     @totalRowCount INT OUTPUT --输出总记录条数
 30 AS
 31 BEGIN
 32     SET NOCOUNT ON;
 33     
 34     DECLARE @RowStart INT; --定义分页起始位置
 35     DECLARE @RowEnd INT; --定义分页结束位置
 36     
 37     DECLARE @Sql NVARCHAR(MAX); --拼接SQL语句
 38     DECLARE @SqlSelectResult NVARCHAR(MAX); --Sql查询结果语句     
 39     DECLARE @SqlCount NVARCHAR(MAX); --Sql Count计数语句
 40     
 41     IF @pageIndex > 0
 42     BEGIN
 43         SET @pageIndex = @pageIndex -1;
 44         SET @RowStart = @pageSize * @pageIndex + 1;
 45         SET @RowEnd = @RowStart + @pageSize - 1;
 46     END
 47     ELSE
 48     BEGIN
 49         SET @RowStart = 1;
 50         SET @RowEnd = 999999;
 51     END
 52     
 53     IF ISNULL(@pageSize, 0) <> 0
 54     BEGIN
 55         SET @sql = 
 56             'With CTE_CustRelatedInfo as (
 57                 SELECT  ROW_NUMBER () OVER (ORDER BY t.CustId ASC)  AS RowNumber, t.*
 58              FROM   VGetCustRelatedInfo2 AS t 
 59                 WHERE 1=1 ';--此处CTE表达式右括号不写，在后面根据条件判断，追加
 60     END
 61     ELSE
 62     BEGIN
 63         SET @sql = 
 64             'SELECT t.*
 65              FROM  VGetCustRelatedInfo2 AS t
 66              WHERE 1=1 ';
 67     END
 68     
 69     IF ISNULL(@custId, '') <> ''
 70     BEGIN
 71         --根据客户id查询
 72         SET @Sql = @Sql + ' AND t.CustId like ''%' + @custId + '%''';
 73     END
 74     
 75     IF ISNULL(@custNam, '') <> ''
 76     BEGIN
 77         --根据客户名称 模糊查询
 78         SET @Sql = @Sql + ' AND t.CustNam like ''%' + @custNam + '%''';
 79     END
 80     
 81     IF ISNULL(@areaNam, '') <> ''
 82     BEGIN
 83         --根据区域、省份名称
 84         SET @Sql = @Sql + ' AND t.AreaNam like ''%' + @areaNam + '%''';
 85     END
 86     
 87     IF ISNULL(@pageSize, 0) <> 0
 88     BEGIN
 89         SET @Sql = @Sql + ') ';
 90         
 91         SET @SqlCount = @Sql + 
 92             ' SELECT @Temp = COUNT(*) FROM CTE_CustRelatedInfo;';
 93         
 94         SET @SqlSelectResult = @Sql + 
 95             ' SELECT * FROM CTE_CustRelatedInfo 
 96               WHERE RowNumber Between ' + CONVERT(VARCHAR(10), @RowStart) 
 97             +
 98             ' And ' + CONVERT(VARCHAR(10), @RowEnd) + ';';
 99         
100         PRINT (@SqlSelectResult);--打印输出sql语句
101         
102         EXEC sp_executesql @SqlSelectResult;--执行sql查询
103         
104         EXEC sp_executesql @SqlCount,
105              N'@Temp int output',
106              @totalRowCount OUTPUT ; --执行count统计
107     END
108     ELSE
109     BEGIN
110         SET @Sql = @sql + ' order by t.CustId ASC ';
111         SET @totalRowCount = 0; --总记录数
112         PRINT (@Sql);--打印输出sql语句
113         EXEC (@Sql);----打印输出sql语句
114     END
115     
116     SET NOCOUNT OFF;
117 END
118 
119 
120 
121 GO

View Code

方案2：在存储过程中先创建临时表，将多个Union(All)前后的sql查询语句的查询结果插入到临时表中，然后操作临时表，最后做其他的处理。