Putting a CTE with Subqueries into a stored procedure

• DaveBriCam

  SSCarpal Tunnel

  Points: 4298

  More actions

  June 22, 2021 at 5:29 pm

  #3899392

  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
  
  

• pietlinden

  SSC Guru

  Points: 63503

  More actions

  June 22, 2021 at 6:02 pm

  #3899407

  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;