How to use a CTE statement in a table-valued function in SQL Server
I have come to understand that some versions of Microsoft OLE DB Provider for SQL Server (mostly on Windows XP) do not support WITH
statement. So, I decided to move my SQL statement into a table-valued function, and call it from my application. Now, I'm stuck. How should I use the INSERT INTO
statement with WITH
? Here's the code I have come with so far, but SQL Server doesn't like it... :-(
CREATE FUNCTION GetDistributionTable
(
@IntID int,
@TestID int,
@DateFrom datetime,
@DateTo datetime
)
RETURNS
@Table_Var TABLE
(
[Count] int,
Result float
)
AS
BEGIN
INSERT INTO @Table_Var ([Count], Result) WITH T(Result)
AS (SELECT ROUND(Result - AVG(Result) OVER(), 1)
FROM RawResults WHERE IntID = @IntID AND DBTestID = @TestID AND Time >= @DateFrom AND Time <= @DateTo)
SELECT COUNT(*) AS [Count],
Result
FROM T
GROUP BY Result
RETURN
END
GO
Solution 1:
Syntax for the CTE in table valued function would be:
CREATE FUNCTION GetDistributionTable
(
@IntID int,
@TestID int,
@DateFrom datetime,
@DateTo datetime
)
RETURNS TABLE
AS
RETURN
(
WITH cte AS
(
SELECT ROUND(Result - AVG(Result) OVER(), 1) Result
FROM RawResults
WHERE IntID = @IntID
AND DBTestID = @TestID
AND Time >= @DateFrom
AND Time <= @DateTo
)
SELECT COUNT(*) AS [Count],
Result
FROM cte
GROUP BY
Result
)
GO
If possible, you can also omit the CTE (WITH
statement), and instead create an inline table valued function that uses subquery:
CREATE FUNCTION GetDistributionTable
(
@IntID int,
@TestID int,
@DateFrom datetime,
@DateTo datetime
)
RETURNS TABLE
AS
RETURN
(
SELECT COUNT(*) AS [Count],
Result
FROM (
SELECT ROUND(Result - AVG(Result) OVER(), 1) Result
FROM RawResults
WHERE IntID = @IntID
AND DBTestID = @TestID
AND Time >= @DateFrom
AND Time <= @DateTo
) t
GROUP BY
Result
)
GO
Your example seems to be using a multi-statement TVF (insert and select), when you have a choice try using the inline TVF because the multi-statement TVF can prevent query optimizer in choosing a better execution plan (performance difference explained here)
Solution 2:
LIKE THIS..
CREATE FUNCTION GetDistributionTable
(
@IntID int,
@TestID int,
@DateFrom datetime,
@DateTo datetime
)
RETURNS
@Table_Var TABLE
(
[Count] int,
Result float
)
AS
BEGIN
WITH T
AS (
select Ticket_Id,COUNT(1) Result from
Customer_Survey
group by MemberID,SiteId,Ticket_Id
)
INSERT INTO @Table_Var ([Count], Result)
SELECT COUNT(*) AS [Count],
Result
FROM T
GROUP BY Result
RETURN
END
GO