Using the system date as a default parameter.
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
1. | Paraemter with NULL value | ||
2. | Specifying a Default Parameter Value |