What is wrong with the CTE syntax in this Sql Server query?

Can anyone explain why Sql Server is complaining about the syntax around the "WITH" clause?

Thanks for any help.

CREATE TABLE TestTable1 (
    Id int not null,
    Version int not null constraint d_Ver default (0),
    [Name] nvarchar(50) not null,

    CONSTRAINT pk_TestTable1 PRIMARY KEY (Id, Version)
);
GO


CREATE TRIGGER trg_iu_UniqueActiveName
ON [dbo].[TestTable1]
AFTER INSERT, UPDATE
AS
IF(UPDATE([Name]))
BEGIN
    IF(
        ( 
开发者_如何学编程            WITH MaxVers AS
                (SELECT Id, Max(Version) AS MaxVersion 
                FROM [dbo].[TestTable1]
                GROUP BY Id)
            SELECT Count(1) 
            FROM [dbo].[TestTable1] t
                INNER JOIN MaxVers ON t.Id = MaxVers.Id AND t.Version = MaxVers.MaxVersion
            WHERE t.[Name] = inserted.[Name]
        )
        > 0
    )
    BEGIN
        DECLARE @name nvarchar(50)
        SELECT @name = [Name] FROM inserted;
        RAISERROR('The name "%s" is already in use.', 16, 1, @name);
    END
END;
GO

Edit 2: For anyone who is curious, here is the CTE version that incorporates all of the great comments below. I think I will switch to the sub-query approach so that I can use the "EXISTS" as suggested.

CREATE TRIGGER trg_iu_UniqueActiveName
ON [dbo].[TestTable1]
AFTER INSERT, UPDATE
AS
IF(UPDATE([Name]))
BEGIN
    DECLARE @cnt [int];
    WITH MaxVers AS
        (SELECT Id, Max(Version) AS MaxVersion 
        FROM [dbo].[TestTable1]
        GROUP BY Id)
        SELECT @cnt = COUNT(1) 
        FROM [dbo].[TestTable1] t
            INNER JOIN MaxVers ON t.Id = MaxVers.Id AND t.Version = MaxVers.MaxVersion
            INNER JOIN [inserted] i ON t.[Id] = MaxVers.[Id]
        WHERE t.[Name] = i.[Name] AND NOT [t].[Id] = [i].[Id] ;
    IF( @cnt > 0)
    BEGIN
        DECLARE @name nvarchar(50)
        SELECT @name = [Name] FROM inserted;
        RAISERROR('The name "%s" is already in use by an active entity.', 16, 1, @name);

        ROLLBACK TRANSACTION;
    END
END;
GO 

Edit 3: Here is the "Exists" version (Note, I think that the select in the error handling part would not work correctly with more than one inserted record):

CREATE TRIGGER trg_iu_UniqueActiveName
ON [dbo].[TestTable1]
AFTER INSERT, UPDATE
AS
IF(UPDATE([Name]))
BEGIN

    IF(EXISTS (
        SELECT t.Id 
        FROM [dbo].[TestTable1] t
            INNER JOIN (
                SELECT Id, Max(Version) AS MaxVersion 
                FROM [dbo].[TestTable1]
                GROUP BY Id) maxVer
                ON t.[Id] = [maxVer].[Id] AND [t].[Version] = [maxVer].[MaxVersion]
            INNER JOIN [inserted] i ON t.[Id] = MaxVer.[Id]
            WHERE [t].[Name] = [i].[Name] AND NOT [t].[Id] = [i].[Id]
        ))
    BEGIN
        DECLARE @name nvarchar(50)
        SELECT @name = [Name] FROM inserted;
        RAISERROR('The name "%s" is already in use by an active entity.', 16, 1, @name);

        ROLLBACK TRANSACTION;
    END
END;
GO

---

The only thing I can figure is that the statement "When a CTE is used in a statement that is part of a batch, the statement before it must be followed by a semicolon." (Transact SQL Reference) means that a CTE can not be used within an IF statement.

BTW, you have two other errors: 1) inserted pseudo table is not included in the first sub-query, even though you reference it in the were clause. 2) Your trigger is assuming a single row is being inserted or updated. It is possible that there would be multiple duplicate names but the raiserror will only report one of them.

EDIT And avoid (select count(*) ...) > when exists (select * ....) will do The exists can stop at the first row.

EDIT 2 Crap. SQL Server trigges default to after triggers. So the row you are checking for existence on already exists in the table when the trigger fire:

CREATE TRIGGER trg_iu_UniqueActiveName
ON [dbo].[TestTable1]
AFTER INSERT, UPDATE
AS
IF(UPDATE([Name]))
BEGIN
    IF EXISTS 
        ( 
            SELECT *
            FROM [dbo].[TestTable1] t
                INNER JOIN inserted i on i.[NAME] = t.[NAME]
                INNER JOIN (SELECT Id, Max(Version) AS MaxVersion 
                    FROM [dbo].[TestTable1]
                    GROUP BY Id) MaxVers ON t.Id = MaxVers.Id AND t.Version = MaxVers.MaxVersion
        )
    BEGIN
        DECLARE @name nvarchar(50)
        SELECT @name = [Name] FROM inserted;
        RAISERROR('The name "%s" is already in use.', 16, 1, @name);
    END
END;
GO

insert into testTable1 (name) values ('Hello')

results in:

Msg 50000, Level 16, State 1, Procedure trg_iu_UniqueActiveName, Line 20
The name "Hello" is already in use.

(1 row(s) affected)

Plus, the raiserror does not perform a rollback, so the row is still there.

---

I don't think that you can use CTEs with inner queries.

Use this as workaround:

DECLARE @cnt int;
WITH MaxVers AS
    (SELECT Id, Max(Version) AS MaxVersion 
    FROM [dbo].[TestTable1]
    GROUP BY Id)
SELECT @cnt = Count(1) 
FROM [dbo].[TestTable1] t
    INNER JOIN MaxVers ON t.Id = MaxVers.Id AND t.Version = MaxVers.MaxVersion
WHERE t.[Name] = inserted.[Name];
IF @cnt > 0
BEGIN
    DECLARE @name nvarchar(50)
    SELECT @name = [Name] FROM inserted;
    RAISERROR('The name "%s" is already in use.', 16, 1, @name);
END

---

Doesn't appear to like the WITH statement inside an IF does it.

Try the following SQL instead:

SELECT COUNT(1)
FROM TestTable1 t1
WHERE t.Name = (SELECT [Name] FROM inserted)
AND t.Version = (SELECT MAX(Version) FROM TestTable1 t2 WHERE t2.Id = t.Id)

Much simpler in my opinion. This doesn't account for multiple rows in the inserted table however. Change it to an IN rather than an = would probably do that.

As others have noted sometimes putting a semi-colon in from of the WITH statement works, but I couldn't get it to in this instance.