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

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