Ik heb begrepen dat sommige versies van Microsoft OLE DB Provider voor SQL Server (meestal op Windows XP) de WITH
-instructie niet ondersteunen. Dus besloot ik mijn SQL-instructie naar een functie met tabelwaarde te verplaatsen en deze vanuit mijn toepassing aan te roepen. Nu zit ik vast. Hoe moet ik de instructie INSERT INTO
gebruiken met WITH
? Hier is de code die ik tot nu toe heb gekregen, maar SQL Server vindt het niet leuk… 🙁
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
Antwoord 1, autoriteit 100%
Syntaxis voor de CTE in tabelwaardefunctie zou zijn:
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
Indien mogelijk kunt u ook de CTE (WITH
-instructie) weglaten en in plaats daarvan een inline tabelwaardefunctie maken die gebruikmaakt van 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
Uw voorbeeld lijkt een TVF met meerdere instructies te gebruiken (invoegen en selecteren), wanneer u de keuze heeft, probeer dan de inline TVF te gebruiken, omdat de TVF met meerdere instructies kan voorkomen dat query-optimalisatie een beter uitvoeringsplan kiest (prestatieverschil uitgelegd hier )
Antwoord 2, autoriteit 26%
LIKE DIT..
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
Antwoord 3
CTE with if else in UDF
USE [SchoolDB]
GO
/****** Object: UserDefinedFunction [dbo].[GetDistributionTable] Script Date: 24-08-2019 05:17:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter FUNCTION [dbo].[GetDistributionTable]
(
@DepartmentName varchar(50) = 'Production'
)
RETURNS
@Table_Var TABLE
( NUM int IDENTITY(1,1),
[ParentEmployeeKey] int,
Result float
)
AS
BEGIN
Declare @Table_Vars table
(
[ParentEmployeeKey] int,
Result float
);
insert into @Table_Vars([ParentEmployeeKey])
select COUNT(1) Result from
[SchoolDB].[dbo].[DimEmployee] where DepartmentName = @DepartmentName
if(@@rowcount >0)
begin
WITH T
AS (
select [ParentEmployeeKey],COUNT(1) Result from
[SchoolDB].[dbo].[DimEmployee] where DepartmentName = @DepartmentName
group by [ParentEmployeeKey]
)
INSERT INTO @Table_Var ([ParentEmployeeKey], Result)
SELECT COUNT(*) AS [Count],
Result
FROM T
GROUP BY Result
end
else
WITH T
AS (
select [ParentEmployeeKey],COUNT(1) Result from
[SchoolDB].[dbo].[DimEmployee] where DepartmentName = @DepartmentName
group by [ParentEmployeeKey]
)
INSERT INTO @Table_Var ([ParentEmployeeKey], Result)
SELECT COUNT(*) AS [Count],
Result
FROM T
GROUP BY Result
RETURN
END
GO