Here u are. If I fill both variables with getdate() it runs without any problem.
– SQL statements for data structure: CoachOnly
DECLARE @Coach_parCurrentDate AS DateTime;
DECLARE @Certificate_parCurrentDate AS DateTime;
SET @Certificate_parCurrentDate = NULL
SET @Coach_parCurrentDate = NULL
– Coach
SELECT
(SELECT COUNT_BIG([aggregation1].[RecordCreated]) FROM [Licence] AS [aggregation1] WHERE [Coach].[Id] = [aggregation1].[refConsolidatedCoachId] AND (dbo.F_LicenceIsValid((SELECT TOP 1
[lookupCoach].[IsArchived] AS [IsArchived]
FROM
[Coach] AS [lookupCoach]
WHERE (
([aggregation1].[refConsolidatedCoachId] = [lookupCoach].[Id]))
), [aggregation1].[NewestLicence], [aggregation1].[ValidityFrom], [aggregation1].[ValidityTo]) = 1)) AS [LicenceCount],
[Coach].[ExcelRow] AS [ExcelRow],
[Coach].[Phone] AS [Phone],
[Coach].[CoachImportPhase2_foto] AS [CoachImportPhase2_foto],
[Coach].[InQSynchronizationLastValueChange] AS [InQSynchronizationLastValueChange],
[Coach].[BirthDate] AS [BirthDate],
[Coach].[ABRA_IsUpdateNeeded] AS [ABRA_IsUpdateNeeded],
[Coach].[CoachImportPhase2_kr_aktual] AS [CoachImportPhase2_kr_aktual],
[Coach].[IsNotificationOn] AS [IsNotificationOn],
[Coach].[Street] AS [Street],
[Coach].[ValidationError] AS [ValidationError],
[Coach].[Name] AS [Name],
[Coach].[InQDisabledUser] AS [InQDisabledUser],
[Coach].[Town] AS [Town],
[Coach].[Origin] AS [Origin],
[Coach].[InQRegion] AS [InQRegion],
[Coach].[IsArchived] AS [IsArchived],
[Coach].[InQUserID] AS [InQUserID],
[Coach].[PhotoFileName] AS [PhotoData],
[Coach].[RecordUpdated] AS [RecordUpdated],
[Coach].[RecordCreatedBy] AS [RecordCreatedBy],
[Coach].[ZipCode] AS [ZipCode],
[Coach].[Id] AS [Id],
[Coach].[RecordCreated] AS [RecordCreated],
(SELECT TOP 1
[LicenceType].[NSACoachClass] AS [NSACoachClass]
FROM
[LicenceType] AS [LicenceType]
INNER JOIN [Licence] AS [Licence] ON
[LicenceType].[Id] = [Licence].[refLicenceTypeId]
INNER JOIN [Coach] AS [Licence_Coach] ON
[Licence].[refConsolidatedCoachId] = [Licence_Coach].[Id]
AND (
([Coach].[Id] = [Licence_Coach].[Id]))
ORDER BY
dbo.F_LicenceIsValid((SELECT TOP 1
[Coach].[IsArchived] AS [IsArchived]
FROM
[Coach] AS [Coach]
WHERE (
([Licence].[refConsolidatedCoachId] = [Coach].[Id]))
), [Licence].[NewestLicence], [Licence].[ValidityFrom], [Licence].[ValidityTo]) DESC,
[LicenceType].[NSACoachClass] ASC
) AS [NSACoachClass],
[Coach].[BirthState] AS [BirthState],
[Coach].[Club] AS [Club],
[Coach].[ExcelName] AS [ExcelName],
[Coach].[RecordUpdatedBy] AS [RecordUpdatedBy],
[Coach].[Email] AS [Email],
[Coach].[Note] AS [Note],
[Coach].[Code] AS [Code],
[Coach].[IdentificationNo] AS [IdentificationNo],
dbo.F_GetCoachLastLicenceRegionId([Coach].[Id]) AS [refLastLicenceRegionID],
dbo.F_GetCertificatesCountByConsolidatedCoachId([Coach].[Id]) AS [CertificatesCount],
(CASE WHEN (NOT([Coach].[PhotoFileName] IS NULL)) THEN ISNULL (CAST (‘/api/private/coach/getphoto/’ AS NVARCHAR(MAX) ), ‘’) + ISNULL (CAST ([Coach].[Id] AS NVARCHAR(MAX) ), ‘’) + ISNULL (CAST (‘/’ AS NVARCHAR(MAX) ), ‘’) + ISNULL (CAST ([Coach].[PhotoFileName] AS NVARCHAR(MAX) ), ‘’) ELSE NULL END) AS [PhotoDataInternalUrl],
[Coach].[Login] AS [Login],
(CASE WHEN [Coach].[refConsolidatedCoachID] IS NULL THEN [Coach].[Id] ELSE [Coach].[refConsolidatedCoachID] END) AS [refConsolidatedCoachIDNotNull],
dbo.F_GetCoachDescription([Coach].[Id]) AS [Description],
dbo.F_GetCoachConsolidationAdeptsCount([Coach].[Id]) AS [ConsolidationAdeptsCount],
dbo.F_GetSubordinateCoachesCountByConsolidatedCoachId([Coach].[Id]) AS [SubordinateCoachesCount],
dbo.F_GetCoachAge([Coach].[Id]) AS [Age],
dbo.F_GetCreditsToDateByConsolidatedCoachId([Coach].[Id], @Coach_parCurrentDate) AS [VirtualCreditsTotal],
[Coach].[ABRA_Firm_ID] AS [ABRA_Firm_ID],
[Coach].[PersonalIdentificationNumber] AS [PersonalIdentificationNumber],
[Coach].[Remark] AS [Remark],
[Coach].[PhotoFileName] AS [PhotoFileName],
[Coach].[CanBeDeleted] AS [CanBeDeleted],
[Coach].[FACR_ID] AS [FACR_ID],
[Coach].[Nuts] AS [Nuts],
[Coach].[PhotoImageID] AS [PhotoImageID],
[Coach].[Surname] AS [Surname],
[Coach].[Mobile] AS [Mobile],
[Coach].[refRegionNutsIdFromNuts] AS [refRegionNutsIdFromNuts],
[Coach].[District] AS [District],
[Coach].[Country] AS [Country],
[Coach].[BirthTown] AS [BirthTown],
[Coach].[refConsolidatedCoachID] AS [refConsolidatedCoachID],
[Coach].[refNutsId] AS [refNutsId],
[Coach].[RegistrationNo] AS [RegistrationNo],
[Coach].[Gender] AS [Gender],
(SELECT COUNT_BIG([aggregation1].[Sequence]) FROM [Licence] AS [aggregation1] WHERE [Coach].[Id] = [aggregation1].[refConsolidatedCoachId] AND (dbo.F_LicenceIsValid((SELECT TOP 1
[lookupCoach].[IsArchived] AS [IsArchived]
FROM
[Coach] AS [lookupCoach]
WHERE (
([aggregation1].[refConsolidatedCoachId] = [lookupCoach].[Id]))
), [aggregation1].[NewestLicence], [aggregation1].[ValidityFrom], [aggregation1].[ValidityTo]) = 1)) AS [ValidLicencesCount]
FROM
[Coach] AS [Coach]
WHERE (
([Coach].[refConsolidatedCoachID] IS NULL) AND
([Coach].[IsArchived] = 0));
– Certificate
SELECT
(CASE WHEN ( ([Certificate].[Passed] = 1) AND (@Certificate_parCurrentDate BETWEEN [Certificate].[DateFinished] AND [Certificate].[EffectiveTo])) THEN 1 ELSE 0 END) AS [IsValid],
[Certificate].[refCertificateTypeId] AS [refCertificateTypeId],
[Certificate].[refConsolidatedCoachId] AS [refConsolidatedCoachId],
[Certificate].[Id] AS [Id]
FROM
[Certificate] AS [Certificate]
WHERE EXISTS (SELECT * FROM [Coach] AS [Coach]
WHERE
[Coach].[Id] = [Certificate].[refConsolidatedCoachId]
AND ((CASE WHEN ( ([Certificate].[Passed] = 1) AND (@Certificate_parCurrentDate BETWEEN [Certificate].[DateFinished] AND [Certificate].[EffectiveTo])) THEN 1 ELSE 0 END) = 1) AND (
([Coach].[refConsolidatedCoachID] IS NULL) AND
([Coach].[IsArchived] = 0)));
– Licence
SELECT
dbo.F_LicenceIsValid((SELECT TOP 1
[lookupCoach].[IsArchived] AS [IsArchived]
FROM
[Coach] AS [lookupCoach]
WHERE (
([Licence].[refConsolidatedCoachId] = [lookupCoach].[Id]))
), [Licence].[NewestLicence], [Licence].[ValidityFrom], [Licence].[ValidityTo]) AS [IsValid],
[Licence].[Id] AS [Id],
[Licence].[refLicenceTypeId] AS [refLicenceTypeId],
[Licence].[refConsolidatedCoachId] AS [refConsolidatedCoachId]
FROM
[Licence] AS [Licence]
WHERE EXISTS (SELECT * FROM [Coach] AS [Coach]
WHERE
[Coach].[Id] = [Licence].[refConsolidatedCoachId]
AND (dbo.F_LicenceIsValid((SELECT TOP 1
[lookupCoach].[IsArchived] AS [IsArchived]
FROM
[Coach] AS [lookupCoach]
WHERE (
([Licence].[refConsolidatedCoachId] = [lookupCoach].[Id]))
), [Licence].[NewestLicence], [Licence].[ValidityFrom], [Licence].[ValidityTo]) = 1) AND (
([Coach].[refConsolidatedCoachID] IS NULL) AND
([Coach].[IsArchived] = 0)));
