Using the system date as a default parameter. : Default Parameter « Store Procedure Function « SQL Server / T-SQL

1. SQL Server / T-SQL
2. Store Procedure Function
3. Default Parameter

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

 

Related examples in the same category