Stored Procedure Help - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Stored Procedure Help

Author

Topic

noonz
Starting Member

33 Posts

Posted - 2010-07-23 : 12:25:07

Can someone help me rewrite this SP to run it on the "LastActivity" column of the cs_Users table, I want it to run it where LastActivity < 2010-01-01

DECLARE mycursor CURSOR FOR

SELECT UserID, UserName

FROM cs_Users

WHERE ((UserName <> 'cek') AND (UserName <> 'charlie') AND (UserName <> 'Anonymous'))

ORDER BY UserName DESC;

OPEN mycursor;

DECLARE @id INT, @name VARCHAR(80), @count INT;

SET @count = 0;

FETCH NEXT FROM mycursor INTO @id, @name

WHILE @@FETCH_STATUS = 0 BEGIN

PRINT 'Deleting ='+ISNULL(@name, 'NULL');

EXEC dbo.cs_User_Delete @id, 'Anonymous';

FETCH NEXT FROM mycursor INTO @id, @name

SET @count = @count + 1;

END

PRINT 'Number of users deleted = ' + CONVERT(VARCHAR(20),ISNULL(@count,0));

CLOSE mycursor

DEALLOCATE mycursor

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-07-23 : 12:39:21

Try this:

DECLARE mycursor CURSOR FOR
SELECT UserID, UserName
FROM cs_Users
WHERE UserName not in ('cek','charlie','Anonymous')
And LastActivity < '2010-01-01'
ORDER BY UserName DESC;

OPEN mycursor;
DECLARE @id INT, @name VARCHAR(80), @count INT;

SET @count = 0;

FETCH NEXT FROM mycursor INTO @id, @name
WHILE @@FETCH_STATUS = 0 BEGIN
PRINT 'Deleting ='+ISNULL(@name, 'NULL');
EXEC dbo.cs_User_Delete @id, 'Anonymous';

FETCH NEXT FROM mycursor INTO @id, @name
SET @count = @count + 1;
END

PRINT 'Number of users deleted = ' + CONVERT(VARCHAR(20),ISNULL(@count,0));

CLOSE mycursor

DEALLOCATE mycursor

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources