Moving data between tables -- sql server (microsoft)

Walter Clayton
3/21/2005 12:18:03 AM

This proc simply returns a row count without actually deleting.

Assuming that's a simple over sight and making an assumption about what it
really looks like...

CREATE PROCEDURE dbo.CleanDB (@rowcount int OUTPUT)
AS

SET NOCOUNT ON
DECLARE @error int

insert into dbo.users_archive
select *
from dbo.users
where (userJoinDate < getdate() - 30)
and (userExpirationData is NULL)
and (userLastPaidDate is NULL)
;

select @error = @@error, @rowcount = -1
if @@error = 0 begin
delete
from dbo.users
where (userJoinDate < getdate() - 30)
and (userExpirationData is NULL)
and (userLastPaidDate is NULL)
select @error = @@error, @rowcount = @@rowcount
;
end

/* Are you sure about this logic? Return 0 if there was an error, else 1? */

IF @error <> 0 BEGIN
RETURN 0
END

RETURN 1
GO

--
Walter Clayton
Any technology distinguishable from magic is insufficiently advanced.

[quoted text, click to view]

Ray
3/22/2005 12:19:04 PM

You'll probably want to add some transaction code to avoid any chance of
data loss.

CREATE PROCEDURE dbo.CleanDB (@rowcount int OUTPUT)
AS

SET NOCOUNT ON
DECLARE @error int

Begin Tran

insert into dbo.users_archive
select *
from dbo.users
where (userJoinDate < getdate() - 30)
and (userExpirationData is NULL)
and (userLastPaidDate is NULL)
;

select @error = @@error

Delete
from dbo.users
where (userJoinDate < getdate() - 30)
and (userExpirationData is NULL)
and (userLastPaidDate is NULL)
select @error = @@error
;

IF @error = 0
BEGIN
COMMIT
RETURN 0
END
ELSE
BEGING
ROLLBACK
RETURN 1
END
GO

[quoted text, click to view]

bb_43 NO[at]SPAM hotmail.com
3/22/2005 3:27:19 PM

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server (microsoft)

sql server (microsoft) : Moving data between tables