Expired date stored procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Expired date stored procedure

Author

Topic

Mpilo
Yak Posting Veteran

52 Posts

Posted - 2008-10-02 : 06:22:33

Hi
I need a stored procedure that will delete the record in the database after a certain time and save it in another database
Pls

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-02 : 06:30:16

1) Which version of SQL Server are you using?
2) Do you have some datetime column for which you can check "age" of record?

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-02 : 06:30:53

Oh, wait!
Haven't we sent that link to you before?

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-02 : 06:30:57

If databases are in same server

CREATE PROC ArchiveExpired
@period int
AS
INSERT INTO db2.schemaname.table
SELECT *
FROM db1.schemaname.table
WHERE DATEDIFF(dd,datefield,GETDATE()) >=@Period

DELETE FROM db1.schemaname.table
WHERE DATEDIFF(dd,datefield,GETDATE()) >=@Period

GO

Mpilo
Yak Posting Veteran

52 Posts

Posted - 2008-10-02 : 06:34:34

i'm using sqlserver 2005 and i have a column 'datecreated' that take a date you saved ,i want to count maybe 30 days then delete that record

quote:
Originally posted by Peso

1) Which version of SQL Server are you using?
2) Do you have some datetime column for which you can check "age" of record?

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-02 : 06:36:37

[code]CREATE PROC ArchiveExpired
@period int
AS
INSERT INTO db2.schemaname.table
SELECT *
FROM db1.schemaname.table
WHERE DATEDIFF(dd,datecreated,GETDATE()) >=@Period

DELETE FROM db1.schemaname.table
WHERE DATEDIFF(dd,datecreated,GETDATE()) >=@Period

GO[/code]

and use it like this
[code]EXEC ArchiveExpired 30[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-02 : 06:39:28

[code]CREATE PROCEDURE dbo.uspArchiveMyRecords
(
@Date DATETIME = NULL
)
AS

SET NOCOUNT ON

IF @Date IS NULL
SET @Date = DATEDIFF(DAY, 30, @Date) -- Default to 30 days
ELSE
SET @Date = DATEDIFF(DAY, 0, @Date)

BEGIN TRY
BEGIN TRANSACTION

INSERT TargetTable
(
{col list here}
)
SELECT {col list here}
FROM SourceTable
WHERE Col1 < @Date

DELETE
FROM SourceTable
WHERE Col1 < @Date

COMMIT TRANSACTION
END TRY

BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH[/code]

E 12°55'05.63"
N 56°04'39.26"

Mpilo
Yak Posting Veteran

52 Posts

Posted - 2008-10-02 : 06:51:31

quote:

Originally posted by Peso

CREATE PROCEDURE dbo.uspArchiveMyRecords
(
	@Date DATETIME = NULL
)
AS

SET NOCOUNT ON

IF @Date IS NULL
	SET @Date = DATEDIFF(DAY, 30, @Date)	-- Default to 30 days
ELSE
	SET @Date = DATEDIFF(DAY, 0, @Date)

BEGIN TRY
	BEGIN TRANSACTION

	INSERT	TargetTable
		(
			{col list here}
		)
	SELECT	{col list here}
	FROM	SourceTable
	WHERE	Col1 < @Date

	DELETE
	FROM	SourceTable
	WHERE	Col1 < @Date

	COMMIT TRANSACTION
END TRY

BEGIN CATCH
	ROLLBACK TRANSACTION
END CATCH

E 12°55'05.63"
N 56°04'39.26"

Mpilo
Yak Posting Veteran

52 Posts

Posted - 2008-10-02 : 06:54:35

I understand the stored procedure my problem now is i want it to be deleted automatical without entering an date.is it possible?

quote:

Originally posted by Peso

CREATE PROCEDURE dbo.uspArchiveMyRecords
(
	@Date DATETIME = NULL
)
AS

SET NOCOUNT ON

IF @Date IS NULL
	SET @Date = DATEDIFF(DAY, 30, @Date)	-- Default to 30 days
ELSE
	SET @Date = DATEDIFF(DAY, 0, @Date)

BEGIN TRY
	BEGIN TRANSACTION

	INSERT	TargetTable
		(
			{col list here}
		)
	SELECT	{col list here}
	FROM	SourceTable
	WHERE	Col1 < @Date

	DELETE
	FROM	SourceTable
	WHERE	Col1 < @Date

	COMMIT TRANSACTION
END TRY

BEGIN CATCH
	ROLLBACK TRANSACTION
END CATCH