Need help with delete stored procedure

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

SQL Server 2005 Forums

Transact-SQL (2005)

Need help with delete stored procedure

Author

Topic

gannina
Starting Member

5 Posts

Posted - 2008-03-09 : 15:28:27

Hi, I'm having a hell of a time trying to get a delete query to work properly. I have an existing query that finds records in the database that have expired and now I want to delete those records. I tried wrapping a delete around it, but it deletes everything.

How can I delete the results of this procedure? Thanks!


SELECT rssName, [Days Old]  FROM
(SELECT rssName, DATEDIFF(D, timestamp, getdate()) AS [Days Old]
FROM tblRSSFeeds) AS feeds
WHERE [Days Old] > @expireTime

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-09 : 15:36:31

SELECT rssName, [Days Old] FROM
(SELECT rssName, DATEDIFF(DAY, timestamp, getdate()) AS [Days Old]
FROM tblRSSFeeds) AS feeds
WHERE [Days Old] > @expireTime

What is the value for @expireTime ?

E 12°55'05.25"
N 56°04'39.16"

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-09 : 15:40:56

Set the expire time and delete like this maybe?. Your column isn't named "timestamp" is it?


Declare @expiretime datetime
Declare @daystodelete int

SET @daystodelete = -90
SET @expiretime = dateadd(d,@daystodelete,getdate())

DELETE tblRSSFeeds
FROM tblRSSFeeds
Where timestamp > @expiretime

Poor planning on your part does not constitute an emergency on my part.

gannina
Starting Member

5 Posts

Posted - 2008-03-09 : 17:54:49

I made a few small changes to the query and it works great, thanks for the help guys! A quick question, in the delete part why do you specify the table and then in the FROM part specify it again? I'm not sure if I need that or not, but I'm just curious as to why its written like that.

New query

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[DeleteExpiredFeeds]
@ExpireTime int
AS

Begin
Declare @ExpireDate datetime
Set @ExpireTime = (@ExpireTime * -1)

-- adds time/date to current date
SET @ExpireDate = dateadd(DAY,@ExpireTime,getdate())

DELETE tblRSSFeeds
FROM tblRSSFeeds
Where creationDate <= @ExpireDate

END

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-09 : 18:04:26

Hmmm..

Force of habit i guess. In most cases if I issue a delete statement, there is a join involved, so naming the table as a habit prevents an urgent restore from backup.

You don't really need this..
Set @TimePeriod = (@TimePeriod * -1)

can also just negate the variable in the function...not a big deal either way.
-- adds time/date to current date
SET @ExpireDate = dateadd(d,-@TimePeriod ,getdate())

Does the expiration period (@TimePeriod) change so frequently it can't be "fixed"?

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources