Sql Server 存儲過程中查詢數據無法使用 Union(All)
- 2019 年 10 月 4 日
- 筆記
微軟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查詢語句的查詢結果插入到臨時表中，然後操作臨時表，最後做其他的處理。
