Error: Recursive common table expression 'EmailLog' does not contain a top-level UNION ALL operator.

Question

I don't get it? I don't see any place to perform an "Union All" in the syntax. What am I missing?

CREATE PROCEDURE SapUser_NdaysBeforeExpirationNotification
    -- Add the parameters for the stored procedure here
(
    @AuditTypeKey nvarchar(50),
    @TimeLapseInMonths int
)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    With AuditResults(SapUserId)
    AS
    (
     Select SapUserId From Audit
     Where TypeKey = @AuditTypeKey And DATEDIFF(month, AttemptDate,  GETDATE()) < 2
    )


    Select * from SapUser 
    Inner Join Audit On vw_SapUser_Retrieve.SapId <> AuditResults.SapUserId
    Where DATEDIFF(month, OriginalTrainingDate,  GETDATE()) > @TimeLapseInMonths 

END

Audit Table (aka EmailLog)

CREATE TABLE [dbo].[Audit](
    [AuditId] [int] NOT NULL,
    [TypeKey] [nvarchar](50) NOT NULL,
    [Description] [nvarchar](250) NULL,
    [AuditDate] [datetime] NOT NULL,
    [SapUserId] [int] NOT NULL,
 CONSTRAINT [PK_EmailLog] PRIMARY KEY CLUSTERED

SapUser Table

CREATE TABLE [dbo].[SapUser](
    [SapId] [int] IDENTITY(70000,1) NOT NULL,
    [Username] [nvarchar](10) NULL,
    [Password] [nvarchar](50) NOT NULL,
    [FirstLogin] [bit] NOT NULL,
    [Roles] [tinyint] NOT NULL,
    [Status] [nvarchar](20) NOT NULL,
    [Title] [nvarchar](20) NULL,
    [FirstName] [nvarchar](50) NOT NULL,
    [LastName] [nvarchar](50) NOT NULL,

...etc

Problem Details

I've got two tables: SapUser and EmailLog. In a specific time frame, if a user doesn't take action their account will be deleted. So in 90, 60, 30, 7, 1 day increments I am sending an email reminding them to take action.

Each time I send an email, I log it in the "Audit" table (was emaillog). Because not all months are 30 days, it might be possible for a person to get two 90 day notices. To prevent this I'm inserting a row into the Audit table.

When I run the routine to find accounts to email, I'm first locating all the accounts in the audit table I sent emails in the last N (90, 60, 30 ..etc) days and removing them from consideration.

Answer 1

With EmailLog(SapUserId)
AS
(
    Select SapUserId 
    From EmailLog
    Where TypeKey = @EmailLogKey 
    And DATEDIFF(month, AttemptDate,  GETDATE()) < 2
)

This is a recursive CTE. Recursive CTEs have a specific form, select from an achro union all the recursive part (in a join). You sure the FROM if correct? Should it be SapUser?

Answer 2

I removed the CTE and created one SQL statement. I believe this is the same thing, the CTE was easier to understand. Hopefully I have this single SQL statement correct.

Select  * from vw_SapUser_Retrieve 
Inner Join Audit On vw_SapUser_Retrieve.SapId = Audit.SapUserId
Where DATEDIFF(month, OriginalTrainingDate,  GETDATE()) > @TimeLapseInMonths AND (Audit.TypeKey <> @AuditTypeKey OR (Audit.TypeKey = @AuditTypeKey AND DATEDIFF(month, Audit.AuditDate,  GETDATE()) > 2))

Answer 3

In the original code, you have:

Select * from SapUser 
Inner Join Audit On vw_SapUser_Retrieve.SapId <> AuditResults.SapUserId
Where DATEDIFF(month, OriginalTrainingDate,  GETDATE()) > @TimeLapseInMonths

This is the only time you referred to the CTE AuditResults and it should be like an table, not as a function. Unless the "Audit" in this line should be "AuditResults" (and SapUser is probably vw_SapUser_Retrieve too).

Anyway, it's possible that the error message is misleading, caused by the CTE reference being used incorrectly.

You've fixed it anyway but I think I can see what went wrong