CREATE PROCEDURE sp_dump_dtspackagelog
@name sysname,
@flags INT = 0, --// Bitmask: 0x01 == preserve latest
@id UNIQUEIDENTIFIER = NULL, --// If non-NULL, use instead of @name.
@versionid UNIQUEIDENTIFIER = NULL, --// If non-NULL, use instead of @id or @name
@lineagefull UNIQUEIDENTIFIER = NULL --// If non-NULL, use instead of @versionid or @id or @name
AS
SET NOCOUNT ON
--// sysadmin only.
IF (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) <> 1)
BEGIN
RAISERROR(15003, 16, 1, N'sysadmin')
RETURN(1) -- Failure
END
--// Don't error if no entries found, as the desired result will be met.
--// DELETE will CASCADE
DELETE sysdtspackagelog
FROM sysdtspackagelog p
WHERE ((@lineagefull IS NULL OR p.lineagefull = @lineagefull)
AND (@versionid IS NULL OR p.versionid = @versionid)
AND (@id IS NULL OR p.id = @id)
AND (@name IS NULL OR p.name = @name))
AND ((@flags & 0x01) = 0
OR p.logdate <
(
SELECT MAX(logdate)
FROM sysdtspackagelog d
WHERE (d.id = p.id)
)
)
RETURN 0 -- SUCCESS