5>
6>
7> CREATE TABLE myusers(
8> UserID varchar(30)NOT NULL PRIMARY KEY,
9> FirstName varchar(30),
10> LastName varchar(30),
11> EmployeeType char(1) NOT NULL,
12> DBAccess varchar(30),
13> StartDate datetime,
14> ExpDate datetime
15> )
16> GO
1>
2>
3> CREATE PROC pr_deluser (@TD datetime )
4> AS
5> DECLARE getuser_curs CURSOR
6> FOR
7> SELECT UserID
8> FROM myusers
9> WHERE ExpDate <= @TD
10> DECLARE @HoldID varchar(30)
11> DECLARE @MyCount int
12> SELECT @MyCount = 0
13> OPEN getuser_curs
14> FETCH NEXT FROM getuser_curs into @HoldID
15> WHILE @@FETCH_STATUS = 0 BEGIN
16> EXEC sp_droplogin @HoldID
17> EXEC pr_copyuser @HoldID
18> SELECT @MyCount = @MyCount + 1
19> FETCH NEXT FROM getuser_curs into @HoldID
20> END
21> DECLARE @MyDisp varchar(50)
22> SELECT @MyDisp = "Number of Users Deleted is " + ltrim(str(@MyCount))
23> PRINT @MyDisp
24> CLOSE getuser_curs
25> DEALLOCATE getuser_curs
26> GO
Cannot add rows to sysdepends for the current object because it depends on the missing object 'pr_copyuser'. The object will still be created.
1>
2>
3> DECLARE @myvalue varchar(12)
4>
5> SELECT @myvalue=CONVERT(varchar(12), getdate())
6>
7> EXEC pr_deluser @myvalue
8> GO
Number of Users Deleted is 0
1>
2> drop PROC pr_deluser;
3> GO
1>
2>
3> drop table myusers;
4> GO