原文: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
运行结果：查询出441条数据，其中Union(all) 之前的sql语句查询结果为101条记录；
Union(all) 之后的sql语句查询结果为330条记录。
2、创建视图，将以上SQL查询语句放在视图中：
ALTER VIEW [dbo].[VGetCustRelatedInfo2]
AS 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 GO
调用视图，运行结果：查询出441条数据，其中Union(all) 之前的sql语句查询结果为101条记录；
Union(all) 之后的sql语句查询结果为330条记录。
3、创建存储过程，代码如下：
/************************************************************
* Code formatted by SoftTree SQL Assistant ?v6.5.258
* Time: 2014/9/12 16:41:46
************************************************************/ GO /****** Object: StoredProcedure [dbo].[SP_GetCustRelatedInfo2] Script Date: 09/12/2014 15:48:17 ******/
SET ANSI_NULLS ON
GO SET QUOTED_IDENTIFIER ON
GO -- =============================================
-- Author: XXX
-- Create date: XXX
-- Description: XXX
-- =============================================
ALTER PROCEDURE [dbo].[SP_GetCustRelatedInfo2]
@custId NVARCHAR(30) --客户编号
,
@custNam NVARCHAR(1000) --客户名称
,
@areaNam NVARCHAR(30)--区域、省份名称
,
@pageSize INT --单页记录条数
,
@pageIndex INT --当前页左索引
,
@totalRowCount INT OUTPUT --输出总记录条数
AS
BEGIN
SET NOCOUNT ON; DECLARE @RowStart INT; --定义分页起始位置
DECLARE @RowEnd INT; --定义分页结束位置 DECLARE @Sql NVARCHAR(MAX); --拼接SQL语句
DECLARE @SqlSelectResult NVARCHAR(MAX); --Sql查询结果语句
DECLARE @SqlCount NVARCHAR(MAX); --Sql Count计数语句 IF @pageIndex > 0
BEGIN
SET @pageIndex = @pageIndex -1;
SET @RowStart = @pageSize * @pageIndex + 1;
SET @RowEnd = @RowStart + @pageSize - 1;
END
ELSE
BEGIN
SET @RowStart = 1;
SET @RowEnd = 999999;
END IF ISNULL(@pageSize, 0) <> 0
BEGIN
SET @sql =
'With CTE_CustRelatedInfo as (
SELECT ROW_NUMBER () OVER (ORDER BY t.CustId ASC) AS RowNumber, t.*
FROM (
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
)
AS t
WHERE 1=1 ';--此处CTE表达式右括号不写，在后面根据条件判断，追加
END
ELSE
BEGIN
SET @sql =
'SELECT t.*
FROM (
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
)
AS t
WHERE 1=1 ';
END IF ISNULL(@custId, '') <> ''
BEGIN
--根据客户id查询
SET @Sql = @Sql + ' AND t.CustId like ''%' + @custId + '%''';
END IF ISNULL(@custNam, '') <> ''
BEGIN
--根据客户名称 模糊查询
SET @Sql = @Sql + ' AND t.CustNam like ''%' + @custNam + '%''';
END IF ISNULL(@areaNam, '') <> ''
BEGIN
--根据区域、省份名称
SET @Sql = @Sql + ' AND t.AreaNam like ''%' + @areaNam + '%''';
END IF ISNULL(@pageSize, 0) <> 0
BEGIN
SET @Sql = @Sql + ') '; SET @SqlCount = @Sql +
' SELECT @Temp = COUNT(*) FROM CTE_CustRelatedInfo;'; SET @SqlSelectResult = @Sql +
' SELECT * FROM CTE_CustRelatedInfo
WHERE RowNumber Between ' + CONVERT(VARCHAR(10), @RowStart)
+
' And ' + CONVERT(VARCHAR(10), @RowEnd) + ';'; PRINT (@SqlSelectResult);--打印输出sql语句 EXEC sp_executesql @SqlSelectResult;--执行sql查询 EXEC sp_executesql @SqlCount,
N'@Temp int output',
@totalRowCount OUTPUT ; --执行count统计
END
ELSE
BEGIN
SET @Sql = @sql + ' order by t.CustId ASC ';
SET @totalRowCount = 0; --总记录数
PRINT (@Sql);--打印输出sql语句
EXEC (@Sql);----打印输出sql语句
END SET NOCOUNT OFF;
END
GO
调用存储过程 ：
DECLARE @totalRowCount INT
EXEC SP_GetCustRelatedInfo2 '','','',10000,1,@totalRowCount OUT
运行结果：查询出330条记录。
以上结果说明：Sql Server 存储过程中查询语句无法直接使用 Union(All)。使用之后，程序不报错，但是查询结果会丢失Union(All)之前的所有查询记录，只保留最后一个Union(All)之后查询语句的查询结果记录。
解决方法：
方案1：先创建视图，将使用Union(All)关键字的sql查询语句放在视图中，然后再存储过程中调用视图。如下：
USE [BPMIS_TEST]
GO /****** Object: StoredProcedure [dbo].[SP_GetCustRelatedInfo2] Script Date: 09/12/2014 15:48:17 ******/
SET ANSI_NULLS ON
GO SET QUOTED_IDENTIFIER ON
GO -- =============================================
-- Author: 张传宁
-- Create date: 2014-9-11
-- Description: 获取对账单评估明细表信息列表
-- =============================================
ALTER PROCEDURE [dbo].[SP_GetCustRelatedInfo2]
@custId NVARCHAR(30) --客户编号
,
@custNam NVARCHAR(1000) --客户名称
,
@areaNam NVARCHAR(30)--区域、省份名称
,
@pageSize INT --单页记录条数
,
@pageIndex INT --当前页左索引
,
@totalRowCount INT OUTPUT --输出总记录条数
AS
BEGIN
SET NOCOUNT ON; DECLARE @RowStart INT; --定义分页起始位置
DECLARE @RowEnd INT; --定义分页结束位置 DECLARE @Sql NVARCHAR(MAX); --拼接SQL语句
DECLARE @SqlSelectResult NVARCHAR(MAX); --Sql查询结果语句
DECLARE @SqlCount NVARCHAR(MAX); --Sql Count计数语句 IF @pageIndex > 0
BEGIN
SET @pageIndex = @pageIndex -1;
SET @RowStart = @pageSize * @pageIndex + 1;
SET @RowEnd = @RowStart + @pageSize - 1;
END
ELSE
BEGIN
SET @RowStart = 1;
SET @RowEnd = 999999;
END IF ISNULL(@pageSize, 0) <> 0
BEGIN
SET @sql =
'With CTE_CustRelatedInfo as (
SELECT ROW_NUMBER () OVER (ORDER BY t.CustId ASC) AS RowNumber, t.*
FROM VGetCustRelatedInfo2 AS t
WHERE 1=1 ';--此处CTE表达式右括号不写，在后面根据条件判断，追加
END
ELSE
BEGIN
SET @sql =
'SELECT t.*
FROM VGetCustRelatedInfo2 AS t
WHERE 1=1 ';
END IF ISNULL(@custId, '') <> ''
BEGIN
--根据客户id查询
SET @Sql = @Sql + ' AND t.CustId like ''%' + @custId + '%''';
END IF ISNULL(@custNam, '') <> ''
BEGIN
--根据客户名称 模糊查询
SET @Sql = @Sql + ' AND t.CustNam like ''%' + @custNam + '%''';
END IF ISNULL(@areaNam, '') <> ''
BEGIN
--根据区域、省份名称
SET @Sql = @Sql + ' AND t.AreaNam like ''%' + @areaNam + '%''';
END IF ISNULL(@pageSize, 0) <> 0
BEGIN
SET @Sql = @Sql + ') '; SET @SqlCount = @Sql +
' SELECT @Temp = COUNT(*) FROM CTE_CustRelatedInfo;'; SET @SqlSelectResult = @Sql +
' SELECT * FROM CTE_CustRelatedInfo
WHERE RowNumber Between ' + CONVERT(VARCHAR(10), @RowStart)
+
' And ' + CONVERT(VARCHAR(10), @RowEnd) + ';'; PRINT (@SqlSelectResult);--打印输出sql语句 EXEC sp_executesql @SqlSelectResult;--执行sql查询 EXEC sp_executesql @SqlCount,
N'@Temp int output',
@totalRowCount OUTPUT ; --执行count统计
END
ELSE
BEGIN
SET @Sql = @sql + ' order by t.CustId ASC ';
SET @totalRowCount = 0; --总记录数
PRINT (@Sql);--打印输出sql语句
EXEC (@Sql);----打印输出sql语句
END SET NOCOUNT OFF;
END GO
方案2：在存储过程中先创建临时表，将多个Union(All)前后的sql查询语句的查询结果插入到临时表中，然后操作临时表，最后做其他的处理。