sql parameterised cte query

Question

I have a query like the following

select  * 
from        (
               select   *
               from     callTableFunction(@paramPrev)
                .....< a whole load of other joins, wheres , etc >........
            ) prevValues
            full join
            (
                select  *
                from    callTableFunction(@paramCurr)
                .....< a whole load of other joins, wheres , etc >........
            ) currValues                on prevValues.Field1 = currValues.Field1
            ....<other joins with the same subselect as the above two with different parameters passed in
where       ........
group by    ....

The following subselect is common to all the subselects in the query bar the @param to the table function.

        select  *
        from    callTableFunction(@param)
            .....< a whole load of other joins, wheres , etc >........

One option is for me to convert this into a function and call the function, but i dont like this as I may be changing the subselect query quite often for.....or I am wondering if there is an alternative using CTE like

with sometable(@param1) as 
(
        select  *
        from    callTableFunction(@param)
                .....< a whole load of other joins, wheres , etc >........
)
select      
        sometable(@paramPrev)       prevValues
        full join sometable(@currPrev)  currValues  on prevValues.Field1 = currValues.Field1
where       ........
group by    ....

Is there any syntax like this or technique I can use like this.

This is in SQL Server 2008 R2

Thanks.

score 2 · Answer 1 · answered Jul 21 '11 at 13:11

What you're trying to do is not supported syntax - CTE's cannot be parameterised in this way.

See books online - http://msdn.microsoft.com/en-us/library/ms175972.aspx.

(values in brackets after a CTE name are an optional list of output column names)

If there are only two parameter values (paramPrev and currPrev), you might be able to make the code a little easier to read by splitting them into two CTEs - something like this:

with prevCTE as  (
          select  *
          from    callTableFunction(@paramPrev)
                  .....< a whole load of other joins, wheres , etc 
 ........ )
,curCTE as  (
          select  *
          from    callTableFunction(@currPrev)
                  .....< a whole load of other joins, wheres , etc 
 ........ ),
 select      
          prevCTE       prevValues
          full join curCTE  currValues  on 
 prevValues.Field1 = currValues.Field1 where 
 ........ group by   
 ....

This is what I did first, but it doesn't solve anything. It simply moves the subselect to the CTE section, I still have duplicated the code. What I am trying to do is reduce the whole query using the common subselect(with the table function call), so if I wanted to make a change to the logic in the subselect I do it in one place. The best method I can think of right now is to wrap it in a function but I don't like this method. — ManiP, Jul 21 '11 at 13:55

score 1 · Answer 2 · answered Sep 15 '11 at 20:23

You should be able to wrap the subqueries up as parameterized inline table-valued functions, and then use them with an OUTER JOIN:

CREATE FUNCTION wrapped_subquery(@param int) -- assuming it's an int type, change if necessary...
RETURNS TABLE
RETURN
    SELECT * FROM callTableFunction(@param)
    .....< a whole load of other joins, wheres , etc ........
GO

SELECT *
FROM
    wrapped_subquery(@paramPrev) prevValues
        FULL OUTER JOIN wrapped_subquery(@currPrev) currValues ON prevValues.Field1 = currValues.Field1
WHERE       ........
GROUP BY    ....

For my use case, I don't have the permission level needed to use CREATE FUNCTION. Still wondering what the best solution would be. — airstrike, Jul 05 '13 at 22:12

bielawski · Answer 3 · 2017-06-15T17:55:56.207

There may be some differences between my example and what you want depending on how your subsequent ON statements are formulated. Since you didn't specify, I assumed that all the subsequent joins were against the first table. In my example I used literals rather than @prev,@current but you can easily substitute variables in place of literals to achieve what you want.

-- Standin function for your table function to create working example.
CREATE FUNCTION TestMe(
    @parm int)
RETURNS TABLE
AS
RETURN
    (SELECT @parm AS N, 'a' AS V UNION ALL
     SELECT @parm + 1,  'b'      UNION ALL
     SELECT @parm + 2,  'c'      UNION ALL
     SELECT @parm + 2,  'd'      UNION ALL
     SELECT @parm + 3,  'e');
go         
-- This calls TestMe first with 2 then 4 then 6... (what you don't want)
-- Compare these results with those below
SELECT t1.N AS AN, t1.V as AV,
       t2.N AS BN, t2.V as BV,
       t3.N AS CN, t3.V as CV
  FROM TestMe(2)AS t1
  FULL JOIN TestMe(4)AS t2 ON t1.N = t2.N
  FULL JOIN TestMe(6)AS t3 ON t1.N = t3.N;

-- Put your @vars in place of 2,4,6 adding select statements as needed
WITH params
    AS (SELECT 2 AS p UNION ALL
        SELECT 4 AS p UNION ALL
        SELECT 6 AS p)
    -- This CTE encapsulates the call to TestMe (and any other joins)
    ,AllData
    AS (SELECT *
          FROM params AS p
          OUTER APPLY TestMe(p.p))  -- See! only coded once
          -- Add any other necessary joins here

    -- Select needs to deal with all the columns with identical names
    SELECT d1.N AS AN, d1.V as AV,
           d2.N AS BN, d2.V as BV,
           d3.N AS CN, d3.V as CV
      -- d1 gets limited to values where p = 2 in the where clause below
      FROM AllData AS d1
      -- Outer joins require the ANDs to restrict row multiplication
      FULL JOIN AllData AS d2 ON d1.N = d2.N
                             AND d1.p = 2 AND d2.p = 4
      FULL JOIN AllData AS d3 ON d1.N = d3.N
                             AND d1.p = 2 AND d2.p = 4 AND d3.p = 6
      -- Since AllData actually contains all the rows we must limit the results
      WHERE(d1.p = 2 OR d1.p IS NULL)
       AND (d2.p = 4 OR d2.p IS NULL)
       AND (d3.p = 6 OR d3.p IS NULL);

What you want to do is akin to a pivot and so the complexity of the needed query is similar to creating a pivot result without using the pivot statement.
Were you to use Pivot, duplicate rows (such as I included in this example) would be aggreagted. This is also a solution for doing a pivot where aggregation is unwanted.

score 0 · Answer 4 · answered May 27 '14 at 09:58

After failing to assign scalar variables before with, i finally got a working solution using a stored procedure and a temp table:

create proc hours_absent(@wid nvarchar(30), @start date, @end date)
as
with T1 as(
  select c from t
),
T2 as(
  select c from T1
)
select c from T2
order by 1, 2
OPTION(MAXRECURSION 365)

Calling the stored procedure:

if object_id('tempdb..#t') is not null drop table #t

create table #t([month] date, hours float)
insert into #t exec hours_absent '9001', '2014-01-01', '2015-01-01'

select * from #t

sql parameterised cte query

4 Answers4