Rendte ind i et mindre, ikke så vel dokumenteret, problem i dag da jeg ville anvende Common Table Expressions (CTE) i et T-SQL testscript.
Min konstruktion var som følger:
DECLARE @Count AS INT
WITH EmployeesWithSameBirthDate ( ID, [Count] ) AS
(
WITH EmployeesWithSameBirthDate ( ID, [Count] ) AS
(
SELECT
dbo.HumanResources.Employee.EmployeeID
, COUNT( dbo.HumanResources.Employee.BirthDate ) AS [Count]
FROM dbo.HumanResources.Employee
GROUP BY dbo.HumanResources.Employee.EmployeeID
HAVING COUNT( dbo.HumanResources.Employee.BirthDate ) > 1
GROUP BY dbo.HumanResources.Employee.EmployeeID
HAVING COUNT( dbo.HumanResources.Employee.BirthDate ) > 1
)
SET @Count = ( SELECT COUNT( 0 ) FROM EmployeesWithSameBirthDate )
IF ( @Count > 0 )
SET @Count = ( SELECT COUNT( 0 ) FROM EmployeesWithSameBirthDate )
IF ( @Count > 0 )
BEGIN
–Bla bla bla
END
ELSE
BEGIN
–Bla bla bla
END
Problemet er, at man kun kan kalde CRUD’s direkte efter CTE’en er oprettet, og dermed ikke ‘SET @Count = …’
Løsningen i denne situation er at erstatte linien hvor @Count bliver assignet med følgende statement:
SELECT @Count = COUNT( 0 ) FROM EmployeesWithSameBirthDate
