I want to combine the result set of my stored procedure.
我想结合我的存储过程的结果集。
I am using UNION, however it doesn't work and throws errors like this:
我正在使用UNION，但它不起作用并抛出这样的错误：
Msg 156, Level 15, State 1, Line 147
Incorrect syntax near the keyword 'UNION'.Msg 156，Level 15，State 1，Line 147关键字'UNION'附近的语法不正确。
Msg 102, Level 15, State 1, Line 251
Incorrect syntax near 'END'.消息102，级别15，状态1，行251'END'附近的语法不正确。
I also tried putting in the temp table however once I started reusing the table, it will state that the temp table already exist.
我也试过放入临时表但是一旦我开始重用表，就会说临时表已经存在了。
Any help will be appreciated, thanks.
任何帮助将不胜感激，谢谢。
Current result:
目前的结果：
lnid result Score_result
aaabbbccc 7B Current
lnid result Score_result
aaabbbccc 4D Override
Expected result:
预期结果：
lnid result Score_result
aaabbbccc 7B Current
aaabbbccc 4D Override
Stored procedure:
存储过程：
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
1 个解决方案
#1
2
You need a BEGIN somewhere.
你需要一个BEGIN。
Since the original stored proc is working fine, the simplest way to do it would be create 2 temp tables and where you're doing the original selects, do select intos. And then do one select at the end where you union both temp tables.
由于原始存储过程工作正常，最简单的方法是创建2个临时表，并且在进行原始选择时，选择int。然后在结合两个临时表的末尾选择一个。
the main body of stored proc will look something like this:
存储过程的主体看起来像这样：
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)
#1
2
You need a BEGIN somewhere.
你需要一个BEGIN。
Since the original stored proc is working fine, the simplest way to do it would be create 2 temp tables and where you're doing the original selects, do select intos. And then do one select at the end where you union both temp tables.
由于原始存储过程工作正常，最简单的方法是创建2个临时表，并且在进行原始选择时，选择int。然后在结合两个临时表的末尾选择一个。
the main body of stored proc will look something like this:
存储过程的主体看起来像这样：
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)