Sql Server 存储过程中查询数据无法使用 Union(All)
Sql Server 存储过程中查询数据无法使用 Union(All)
张传宁IT讲堂
发布于 2019-09-17 17:00:54
发布于 2019-09-17 17:00:54
微软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运行结果：查询出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调用视图，运行结果：查询出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调用存储过程 ：
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方案2：在存储过程中先创建临时表，将多个Union(All)前后的sql查询语句的查询结果插入到临时表中，然后操作临时表，最后做其他的处理。
本文参与 腾讯云自媒体分享计划，分享自作者个人站点/博客。
原始发表：2014-09-12 ，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
相关产品与服务
对象存储
对象存储（Cloud Object Storage，COS）是由腾讯云推出的无目录层次结构、无数据格式限制，可容纳海量数据且支持 HTTP/HTTPS 协议访问的分布式存储服务。腾讯云 COS 的存储桶空间无容量上限，无需分区管理，适用于 CDN 数据分发、数据万象处理或大数据计算与分析的数据湖等多种场景。