组合存储过程的结果集
我想结合我的存储过程的结果集。
我使用的是UNION ，但它不起作用并抛出如下错误：
Msg 156，Level 15，State 1，Line 147
关键字'UNION'附近的语法不正确。
Msg 102，Level 15，State 1，Line 251
'END'附近的语法错误。
我也尝试过放入临时表，但是一旦我开始重用表，它将声明临时表已经存在。
任何帮助将不胜感激，谢谢。
当前结果：
lnid result Score_result
aaabbbccc 7B Current
lnid result Score_result
aaabbbccc 4D Override
预期结果：
lnid result Score_result
aaabbbccc 7B Current
aaabbbccc 4D Override
存储过程：
CREATE PROCEDURE [dbo].[Results]
--@LanID AS VARCHAR(50) = NULL,
--@period AS VARCHAR(50) = NULL,
--@strtdt AS DATETIME,
--@enddt AS DATETIME
AS
BEGIN
DECLARE @LanID AS VARCHAR(50)
DECLARE @period AS VARCHAR(50)
DECLARE @strtdt AS DATETIME
DECLARE @enddt AS DATETIME
SET @LanID = 'aaabbbccc'
SET @period = 'H1'
SET @strtdt ='2015-10-01'
SET @enddt = DATEADD(MONTH, 11, @strtdt )
IF NOT EXISTS (SELECT *
FROM overridetable
WHERE AGENT = @LanID
AND KRA_HALF = @strtdt
AND BIT_CURRENT = 1
AND IS_FULL_YEAR = 0)
BEGIN
IF NOT EXISTS (SELECT *
FROM periodictable
WHERE repdate BETWEEN @strtdt AND @enddt
AND lnid = @LanID
AND rep_period = @period)
BEGIN
IF @period IN ('H1', 'H2')
(SELECT
lnid, CAST (ROUND(SUM(CAST(numscre AS FLOAT)) / SUM(CAST(counterOfRep AS FLOAT)), 0) AS VARCHAR) + ' ' + CAST (CASE WHEN ROUND(SUM(CAST(cmpscre AS FLOAT)) / SUM(CAST(counterOfRep AS FLOAT)),0) = 1 THEN 'A'
WHEN ROUND(SUM(CAST(cmpscre AS FLOAT)) / SUM(CAST(counterOfRep AS FLOAT)),0) = 2 THEN 'B'
WHEN ROUND(SUM(CAST(cmpscre AS FLOAT)) / SUM(CAST(counterOfRep AS FLOAT)),0) = 3 THEN 'C' END AS VARCHAR) Result
,'Current' as 'Score_Result'
FROM
(SELECT
repdate, lnid, team_Code, numscre,
CASE
WHEN cmpscre = 'A' THEN 1
WHEN cmpscre = 'B' THEN 2
WHEN cmpscre = 'C' THEN 3
END AS cmpscre,
rep_period, 1 AS CounterOfRep,
CASE
WHEN rep_period IN ('Q1','Q2') THEN 'H1'
WHEN rep_period IN ('Q3','Q4') THEN 'H2'
END AS HalfYearPeriod
FROM
periodictable
WHERE
rep_period LIKE 'Q%'
AND repdate BETWEEN @strtdt AND @enddt) tmp
WHERE
lnid = @LanID
AND halfyearperiod = @period
GROUP BY
HalfYearPeriod, lnid, team_code)
ELSE IF @period IN ('FY')
(SELECT
lnid, CAST (ROUND(SUM(CAST(numscre AS FLOAT)) / SUM(CAST(counterOfRep AS FLOAT)),0) AS VARCHAR) + ' ' + CAST (CASE WHEN ROUND(SUM(CAST(cmpscre AS FLOAT)) / SUM(CAST(counterOfRep AS FLOAT)),0) = 1 THEN 'A'
WHEN ROUND(SUM(CAST(cmpscre AS FLOAT)) / SUM(CAST(counterOfRep AS FLOAT)),0) = 2 THEN 'B'
WHEN ROUND(SUM(CAST(cmpscre AS FLOAT)) / SUM(CAST(counterOfRep AS FLOAT)),0) = 3 THEN 'C' END AS VARCHAR) Result
,'Current' as 'Score_Result'
FROM
(SELECT
repdate, lnid, team_Code, numscre,
CASE
WHEN cmpscre = 'A' THEN 1
WHEN cmpscre = 'B' THEN 2
WHEN cmpscre = 'C' THEN 3
END AS cmpscre,
rep_period, 1 AS CounterOfRep,
'FY' AS FullYearPeriod
FROM
periodictable
WHERE
rep_period LIKE 'Q%'
AND repdate BETWEEN @strtdt AND @enddt) tmp
WHERE
lnid = @LanID
AND FullYearPeriod = @period
GROUP BY
FullYearPeriod, lnid, team_code)
ELSE
(SELECT lnid, CAST (numscre AS VARCHAR) + ' ' +
CAST (cmpscre AS VARCHAR) Result
,'Current' as 'Score_Result'
FROM periodictable
WHERE lnid = @LanID
AND rep_period = @period
AND rep_period LIKE 'Q%'
AND repdate BETWEEN @strtdt AND @enddt)
END
ELSE
BEGIN
(SELECT lnid, CAST(numscre AS VARCHAR) + ' ' + CAST(cmpscre AS VARCHAR) AS Result
,'Current' as 'Score_Result'
FROM periodictable
WHERE repdate between @strtdt AND @enddt
AND lnid = @LanID
AND rep_period = @period)
END
END
ELSE
BEGIN
(SELECT Agent AS lnid, CAST(CAST(ROUND(Numerical_Override, 0 ) AS INT) AS VARCHAR) + ' ' +
CAST((CASE WHEN Competency_Override = 1 THEN 'A'
WHEN Competency_Override = 2 THEN 'B'
WHEN Competency_Override = 3 THEN 'C' ELSE '*' END ) AS VARCHAR) AS Result
,'Current' as 'Score_Result'
FROM overridetable
WHERE AGENT = @LanID AND KRA_HALF = @strtdt AND BIT_CURRENT = 1 AND IS_FULL_YEAR = 0)
END
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
IF EXISTS
(SELECT *
FROM overridetable
WHERE AGENT = @LanID AND KRA_HALF = @strtdt AND BIT_CURRENT = 1 AND IS_FULL_YEAR = 0)
BEGIN
IF NOT EXISTS
(SELECT *
FROM periodictable
WHERE repdate BETWEEN @strtdt AND @enddt
AND lnid = @LanID
AND rep_period = @period)
BEGIN
IF @period IN ('H1', 'H2')
(SELECT lnid, CAST (ROUND(SUM(CAST(numscre AS FLOAT)) / SUM(CAST(counterOfRep AS FLOAT)),0) AS VARCHAR) + ' ' +
CAST (CASE WHEN ROUND(SUM(CAST(cmpscre AS FLOAT)) / SUM(CAST(counterOfRep AS FLOAT)),0) = 1 THEN 'A'
WHEN ROUND(SUM(CAST(cmpscre AS FLOAT)) / SUM(CAST(counterOfRep AS FLOAT)),0) = 2 THEN 'B'
WHEN ROUND(SUM(CAST(cmpscre AS FLOAT)) / SUM(CAST(counterOfRep AS FLOAT)),0) = 3 THEN 'C' END AS VARCHAR) Result
,'Override' as 'Score_Result'
FROM (SELECT repdate, lnid, team_Code, numscre,
CASE WHEN cmpscre = 'A' THEN 1
WHEN cmpscre = 'B' THEN 2
WHEN cmpscre = 'C' THEN 3 END AS cmpscre,
rep_period, 1 AS CounterOfRep,
CASE WHEN rep_period IN ('Q1','Q2') THEN 'H1'
WHEN rep_period IN ('Q3','Q4') THEN 'H2' END AS HalfYearPeriod
FROM periodictable
WHERE rep_period LIKE 'Q%'
AND repdate BETWEEN @strtdt AND @enddt)tmp
WHERE lnid = @LanID
AND halfyearperiod = @period
GROUP BY HalfYearPeriod,lnid, team_code)
ELSE IF @period IN ('FY')
(SELECT lnid, CAST (ROUND(SUM(CAST(numscre AS FLOAT)) / SUM(CAST(counterOfRep AS FLOAT)),0) AS VARCHAR) + ' ' +
CAST (CASE WHEN ROUND(SUM(CAST(cmpscre AS FLOAT)) / SUM(CAST(counterOfRep AS FLOAT)),0) = 1 THEN 'A'
WHEN ROUND(SUM(CAST(cmpscre AS FLOAT)) / SUM(CAST(counterOfRep AS FLOAT)),0) = 2 THEN 'B'
WHEN ROUND(SUM(CAST(cmpscre AS FLOAT)) / SUM(CAST(counterOfRep AS FLOAT)),0) = 3 THEN 'C' END AS VARCHAR) Result
,'Override' as 'Score_Result'
FROM (SELECT repdate, lnid, team_Code, numscre,
CASE WHEN cmpscre = 'A' THEN 1
WHEN cmpscre = 'B' THEN 2
WHEN cmpscre = 'C' THEN 3 END AS cmpscre,
rep_period, 1 AS CounterOfRep,'FY' AS FullYearPeriod
FROM periodictable
WHERE rep_period LIKE 'Q%'
AND repdate BETWEEN @strtdt AND @enddt)tmp
WHERE lnid = @LanID
AND FullYearPeriod = @period
GROUP BY FullYearPeriod,lnid, team_code)
ELSE
(SELECT lnid, CAST (numscre AS VARCHAR) + ' ' +
CAST (cmpscre AS VARCHAR) Result
,'Override' as 'Score_Result'
FROM periodictable
WHERE lnid = @LanID
AND rep_period = @period
AND rep_period LIKE 'Q%'
AND repdate BETWEEN @strtdt AND @enddt)
END
ELSE
BEGIN
(SELECT lnid, CAST(numscre AS VARCHAR) + ' ' + CAST(cmpscre AS VARCHAR) AS Result
,'Override' as 'Score_Result'
FROM periodictable
WHERE repdate between @strtdt AND @enddt
AND lnid = @LanID
AND rep_period = @period)
END
END
ELSE
BEGIN
(SELECT @LanID AS lnid, '--' AS Result,
'Override' as 'Score_Result' )
END
END
你需要一个BEGIN地方。
由于原来的存储过程工作正常，最简单的方法就是创建2个临时表，并且在做原始select ， select into s。 然后做在最后，你一个选择union都临时表。
存储过程的主体看起来像这样：
create temp table 1 ......
create temp table 2 .........
IF NOT EXISTS
(SELECT *
FROM overridetable
WHERE AGENT = @LanID AND KRA_HALF = @strtdt AND BIT_CURRENT = 1 AND IS_FULL_YEAR = 0)
BEGIN
IF NOT EXISTS (SELECT *
FROM periodictable
WHERE repdate BETWEEN @strtdt AND @enddt
AND lnid = @LanID
AND rep_period = @period)
BEGIN
IF @period IN ('H1', 'H2')
select into temp table 1
ELSE IF @period IN ('FY')
select into temp table 1
ELSE
select into temp table 1
END
ELSE
BEGIN
select into temp table 1
END
END
ELSE
BEGIN
select into temp table 1
END
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
IF NOT EXISTS
(SELECT *
FROM overridetable
WHERE AGENT = @LanID AND KRA_HALF = @strtdt AND BIT_CURRENT = 1 AND IS_FULL_YEAR = 0)
BEGIN
IF NOT EXISTS (SELECT *
FROM periodictable
WHERE repdate BETWEEN @strtdt AND @enddt
AND lnid = @LanID
AND rep_period = @period)
BEGIN
IF @period IN ('H1', 'H2')
select into temp table 2
ELSE IF @period IN ('FY')
select into temp table 2
ELSE
select into temp table 2
END
ELSE
BEGIN
select into temp table 2
END
END
ELSE
BEGIN
select into temp table 2
END
(select * from temp table 1) union (select * from temp table 2)