Putting a CTE with Subqueries into a stored procedure

  • Is there a way to place the following code into a SELECT routine in a stored procedure?:

    --- Using a CTE (Common Table Expression) "t" to gather all the data from the 
    --- [a2hr].[dbo].[PyRlByPeriod] "PRBP" table

    WITH t ([Year], Period, week, DIALER) AS

    (SELECT PRBP.Year
    , PRBP.Period
    , PRBP.WEEK
    , PRBP.DIALER
    FROM [a2hr].[dbo].[PyRlByPeriod] PRBP)

    SELECT t.Year
    , t.Period
    , t.WEEK
    , t.DIALER
    FROM t

    GROUP BY Year, Period, Dialer

    UNION

    SELECT t.Year
    , t.Period
    , t.WEEK
    , t.DIALER
    FROM t

    GROUP BY Year, Period, Dialer

  • Why not? You could add filter parameters etc.

    CREATE PROCEDURE MyProc
                @param1 VARCHAR(10),
    @param2 int
    AS
    WITH myCTE(col1, col2, col3)
    AS
    (SELECT col1, col2, col3
    FROM table1
    UNION ALL
    SELECT col, col2, col3
    FROM table2)

    SELECT col1,col2, col3
    FROM myCTE
    WHERE col1 = @param1
    AND col2 > @param2;

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply