Combine result set from stored procedure
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:
Msg 156, Level 15, State 1, Line 147
Incorrect syntax near the keyword 'UNION'.
Msg 102, Level 15, State 1, Line 251
Incorrect syntax near '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
You need a BEGIN somewhere.
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 select s, do select into s. And then do one select at the end where you union both temp tables.
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)
上一篇: 存储过程在执行时给出错误
下一篇: 组合存储过程的结果集