CODEWITHSUNDEEP
sqlcommon-table-expression
Ice_Drop
Ice_Drop

Reputation: 462

Table Valued Function with parameter CTE expression result

Is it possible to have a select statement that includes tvf and its parameter is the result of a CTE expression? Here is my code:

 ;with
 date_cte as
 (
    SELECT * FROM ExplodeDates('2012-10-09','2012-10-12')
 )
SELECT * FROM ufn_GET_ATTENDANCE
(
    SELECT QUOTENAME(CONVERT(varchar(10),thedate,120),'''') thedate FROM date_cte
)

When I run this query, the error is Incorrect syntax near the keyword 'SELECT'. and Incorrect syntax near ')'.

Does it possible? Or I have some misconception with the CTE. Thank you!

Upvotes: 3

Views: 2318

Answers (2)

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16904

If you have CTE in your SQLServer version then have CROSS APPLY and OUTER APPLY operators too

;with date_cte as
 (
  SELECT * FROM ExplodeDates('2012-10-09','2012-10-12')
 )
  SELECT c.*
  FROM date_cte CROSS APPLY ufn_GET_ATTENDANCE(QUOTENAME(CONVERT(varchar(10), thedate, 120), '''')) c

Demo on SQLFiddle

Upvotes: 1

RichardTheKiwi
RichardTheKiwi

Reputation: 107716

If ufn_GET_ATTENDANCE were to take a scalar input, you could bracket the inner query to feed it a scalar value.

;with
 date_cte as
 (
    SELECT * FROM ExplodeDates('2012-10-09','2012-10-12')
 )
SELECT * FROM ufn_GET_ATTENDANCE
(
    (SELECT QUOTENAME(CONVERT(varchar(10),thedate,120),'''') thedate FROM date_cte)
)

However, because ufn_GET_ATTENDANCE takes a user-defined table type as parameter, it is impossible to pass a CTE result. Not even a compatible table variable unless it is of the EXACT defined type.

See these examples:

create type ud_type as table (A int)
GO
create function table_in(@in ud_type readonly)
returns table as
return select * from @in
GO

declare @tbl ud_type;
select * from table_in(@tbl)
-- ok
GO

declare @tbl table (A int);
select * from table_in(@tbl)
-- Operand type clash: table is incompatible with ud_type:

Upvotes: 0

Related Questions