Run a SP with a datetime interval from a table

Hello,

I have something i called a deletelist, inside this list is items that is waiting to be deleted.

The deletelist looks like this:
username nvarchar(20),
itemtype int,
deletedate datetime

The SP that runs once everyday and deletes post is calledData_DeleteFromDeleteList.

Inside of this SP i have afew delete functions like this one:
DELETEFROM reg_userWHERE(username=(SELECT usernameFROM data_deletelistWHERE(deletedate<getdate()AND itemtype= 0)))

Now i want to add a new delete function to this list, this function is to execute a SP when the itemtype occurs, like this:

SELECTEXEC [dbo].[Data_CompletlyDeleteAUser] @.UserName= username, @.QResult= @.QResultOUTPUTFROM data_deletelistWHERE(username=(SELECT usernameFROM data_deletelistWHERE(deletedate<getdate()AND itemtype= 2)))

any idea of how i can rewrite this function so it works??
in this SP im not using the @.QResult for anything, thats in another SP

At the end of this SP, i just delete all the old records from the deletelist like this,

DELETEFROM data_deletelistWHERE(deletedate<getdate())

I don't think there's a way to do something like this without a cursor, and those should be avoided if possible. You can do a couple of things here

1.) maybe you can change the proc Data_CompletelyDeleteAUser to be Data_CompletelyDeleteAllUsersWithItemType and pass it 2, then in your proc, you would do whatever it does for ONE user, but for all of them that match that type from the deletelist table.

2.) you can get rid of the proc altogether (if nothing else is using it) and jsut embed the logic into the main SP, which will allow for the operation to be set based.

You can use the cursor if this proc will run once nightly and there aren't too many records, but if 1 or 2 works for you, I'd go with those above all else.

Once 2008 comes out we'll be able to pass Tabular data to stored procedures so this won't be an issue anymore.

--D

|||

The problem with combining the procedures is that, the Data_CompletelyDeleteAUser contains over 1000 lines of code =/