tipos - Cómo usar una declaración CTE en una función con valores de tabla en SQL Server

like en sql ejemplos

Question

Entiendo que algunas versiones del Proveedor Microsoft OLE DB para SQL Server (principalmente en Windows XP) no son compatibles WITH declaración WITH . Entonces, decidí mover mi declaración SQL a una función con valores de tabla y llamarla desde mi aplicación. Ahora estoy atascado. ¿Cómo debo usar la INSERT INTO con WITH ? Aquí está el código con el que he venido hasta ahora, pero a SQL Server no le gusta ... :-(

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

Answer 1

ME GUSTA ESTO..

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

Answer 2

Puede omitir el CTE (instrucción WITH ) y, en su lugar, crear una función con valor de tabla en línea que utilice la subconsulta:

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

La sintaxis para el CTE en función de tabla de valores sería:

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

Su ejemplo parece estar usando un TVF de múltiples instrucciones (insertar y seleccionar), cuando tiene la opción, intente usar el TVF en línea porque el TVF de múltiples declaraciones puede evitar que el optimizador de consultas elija un mejor plan de ejecución (la diferencia de rendimiento se explica here )