lucas lucas - 6 months ago 7
SQL Question

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

Answer

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 selects, do select intos. 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)