首页 > 代码库 > Sql Server 存储过程中查询数据无法使用 Union(All)
Sql Server 存储过程中查询数据无法使用 Union(All)
原文:Sql Server 存储过程中查询数据无法使用 Union(All)
View CodeView CodeView CodeView Code
微软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.CustIdWHERE 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.CustIdWHERE 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 ci22 INNER JOIN AreaInfo AS ai23 ON ci.AreaCode = ai.AreaCode24 INNER JOIN AreaInfo AS ai225 ON ai.PareaCode = ai2.AreaCode26 LEFT JOIN CustContract AS cc27 ON cc.CustId = ci.CustId28 LEFT JOIN CustArApTotal AS caat29 ON ci.CustId = caat.CustId30 WHERE ci.CustCatagory = 131 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 ci52 INNER JOIN AreaInfo AS ai53 ON ci.AreaCode = ai.AreaCode54 INNER JOIN AreaInfo AS ai255 ON ai.PareaCode = ai2.AreaCode56 INNER JOIN CustContract AS cc57 ON cc.CustId = ci.CustId58 LEFT JOIN CustArApTotal AS caat59 ON ci.CustId = caat.CustId60 WHERE ci.CustCatagory = 261 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 ci113 INNER JOIN AreaInfo AS ai114 ON ci.AreaCode = ai.AreaCode115 INNER JOIN AreaInfo AS ai2116 ON ai.PareaCode = ai2.AreaCode117 INNER JOIN CustContract AS cc118 ON cc.CustId = ci.CustId119 LEFT JOIN CustArApTotal AS caat120 ON ci.CustId = caat.CustId121 WHERE ci.CustCatagory = 2122 )123 AS t 124 WHERE 1=1 ‘;--此处CTE表达式右括号不写，在后面根据条件判断，追加125 END126 ELSE127 BEGIN128 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 ci148 INNER JOIN AreaInfo AS ai149 ON ci.AreaCode = ai.AreaCode150 INNER JOIN AreaInfo AS ai2151 ON ai.PareaCode = ai2.AreaCode152 LEFT JOIN CustContract AS cc153 ON cc.CustId = ci.CustId154 LEFT JOIN CustArApTotal AS caat155 ON ci.CustId = caat.CustId156 WHERE ci.CustCatagory = 1157 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 ci178 INNER JOIN AreaInfo AS ai179 ON ci.AreaCode = ai.AreaCode180 INNER JOIN AreaInfo AS ai2181 ON ai.PareaCode = ai2.AreaCode182 INNER JOIN CustContract AS cc183 ON cc.CustId = ci.CustId184 LEFT JOIN CustArApTotal AS caat185 ON ci.CustId = caat.CustId186 WHERE ci.CustCatagory = 2187 )188 AS t189 WHERE 1=1 ‘;190 END191 192 IF ISNULL(@custId, ‘‘) <> ‘‘193 BEGIN194 --根据客户id查询195 SET @Sql = @Sql + ‘ AND t.CustId like ‘‘%‘ + @custId + ‘%‘‘‘;196 END197 198 IF ISNULL(@custNam, ‘‘) <> ‘‘199 BEGIN200 --根据客户名称 模糊查询201 SET @Sql = @Sql + ‘ AND t.CustNam like ‘‘%‘ + @custNam + ‘%‘‘‘;202 END203 204 IF ISNULL(@areaNam, ‘‘) <> ‘‘205 BEGIN206 --根据区域、省份名称207 SET @Sql = @Sql + ‘ AND t.AreaNam like ‘‘%‘ + @areaNam + ‘%‘‘‘;208 END209 210 IF ISNULL(@pageSize, 0) <> 0211 BEGIN212 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 END231 ELSE232 BEGIN233 SET @Sql = @sql + ‘ order by t.CustId ASC ‘;234 SET @totalRowCount = 0; --总记录数235 PRINT (@Sql);--打印输出sql语句236 EXEC (@Sql);----打印输出sql语句237 END238 239 SET NOCOUNT OFF;240 END241 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 END108 ELSE109 BEGIN110 SET @Sql = @sql + ‘ order by t.CustId ASC ‘;111 SET @totalRowCount = 0; --总记录数112 PRINT (@Sql);--打印输出sql语句113 EXEC (@Sql);----打印输出sql语句114 END115 116 SET NOCOUNT OFF;117 END118 119 120 121 GO
方案2：在存储过程中先创建临时表，将多个Union(All)前后的sql查询语句的查询结果插入到临时表中，然后操作临时表，最后做其他的处理。
Sql Server 存储过程中查询数据无法使用 Union(All)
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。