spDelete_EmailTemplate

CREATE procedure [dbo].[spDelete_EmailTemplate]
(
    @PKID int,
    @CurrentUsersID    int
)
as
BEGIN
    declare @EmailListID as int
    declare @oldname as nvarchar(92)
    declare @newname as nvarchar(92)
    SELECT @oldname = [Name]
    FROM [dbo].EmailTemplate 
    where id=@PKID

    exec spUniqueName @oldname, @newname OUTPUT

    BEGIN TRAN
        UPDATE [dbo].EmailTemplate 
        SET [Name] = @newname,
        [EXPORTDEFINITIONID] = null,
        [deleted] = 1
        where id=@PKID;

        SET @EmailListID = (Select [EmailListID] FROM dbo.EmailTemplate_EmailList WHERE EmailTemplateID = @PKID);

        exec [dbo].spDelete_EmailList @EmailListID , @CurrentUsersID;

        -- Delete emails using this template that haven't been sent yet.
        -- They won't show in the GUI if they aren't sent.
        update dbo.Email set Deleted = 1
        from dbo.Email e
        left outer join dbo.EmailJob ej on ej.EmailID = e.ID
        where e.EmailTemplateID = @PKID and isnull(ej.Status,1) < 2;

    IF @@ERROR = 0
    BEGIN
        COMMIT TRAN
    END
    ELSE
    BEGIN
        ROLLBACK TRAN
    END
END