Delete records based on different values

Last Post 07 Dec 2008 02:44 PM by trans53. 2 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
trans53
New Member
New Member

--
03 Dec 2008 07:52 PM
Hi guys,
I have a table where all records separated by 2 different values.
Let’s say I want to purge records with value = 2 where createdate < getdate() -30 and purge records with value = 1 where createdate < getdate() – 7.

I just created this sp that will do a purge for all records but I was wondering if I could do the requirement above in one stored procedure. Can you tell me please if it’s possible?
Thanks

This is the sp I just created. Also please let me know if the logic looks right to you or could do better here.

CREATE PROCEDURE [dbo].[purge_loggingdata]
@CreateDateMax DATETIME,
@RowCountMax INT,
@RunDuration DATETIME = '1:00:00'
AS

SET NOCOUNT ON

DECLARE @TimeLimit datetime,
@Rows int

SELECT @TimeLimit = GETDATE() + @RunDuration,
@Rows = 1


WHILE (@Rows > 0)
BEGIN

BEGIN TRY
DELETE TOP(@RowCountMax)
FROM dbo.table
WHERE CreateDate < @CreateDateMax

SELECT @Rows = @@ROWCOUNT

IF GETDATE() > @TimeLimit
GOTO END_TRANSACTION
END TRY

BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage
PRINT 'An error occurred in stored procedure dbo.purge_loggingdata'
RETURN (1)
END CATCH
END

END_TRANSACTION:

RETURN (0)
trans53
New Member
New Member

--
07 Dec 2008 02:44 PM
i did test this stored procedure and everything is working fine except when i try to test for an error it always picks the next loop and runs sp until all records processed.
I think it's because of the END after the END CATCH.

Is there a way to exit the WHILE loop if the error occured within the first loop?
Sorry for so many stupid questions, just trying to make this work.

Thanks
trans53
New Member
New Member

--
07 Dec 2008 08:37 PM
Good point, thank you


Acceptable Use Policy
---