SQL Server/T-SQL/Store Procedure Function/Default Parameter
Paraemter with NULL value
1> create table employee(
2> ID int,
3> name nvarchar (10),
4> salary int,
5> start_date datetime,
6> city nvarchar (10),
7> region char (1))
8> GO
1>
2> insert into employee (ID, name, salary, start_date, city, region)
3> values (1, "Jason", 40420, "02/01/94", "New York", "W")
4> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (2, "Robert",14420, "01/02/95", "Vancouver","N")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (3, "Celia", 24020, "12/03/96", "Toronto", "W")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (4, "Linda", 40620, "11/04/97", "New York", "N")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (5, "David", 80026, "10/05/98", "Vancouver","W")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (6, "James", 70060, "09/06/99", "Toronto", "N")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (7, "Alison",90620, "08/07/00", "New York", "W")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (8, "Chris", 26020, "07/08/01", "Vancouver","N")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (9, "Mary", 60020, "06/09/02", "Toronto", "W")
3> GO
(1 rows affected)
1>
2> select * from employee
3> GO
ID name salary start_date city region
----------- ---------- ----------- ----------------------- ---------- ------
1 Jason 40420 1994-02-01 00:00:00.000 New York W
2 Robert 14420 1995-01-02 00:00:00.000 Vancouver N
3 Celia 24020 1996-12-03 00:00:00.000 Toronto W
4 Linda 40620 1997-11-04 00:00:00.000 New York N
5 David 80026 1998-10-05 00:00:00.000 Vancouver W
6 James 70060 1999-09-06 00:00:00.000 Toronto N
7 Alison 90620 2000-08-07 00:00:00.000 New York W
8 Chris 26020 2001-07-08 00:00:00.000 Vancouver N
9 Mary 60020 2002-06-09 00:00:00.000 Toronto W
(9 rows affected)
1>
2> drop PROCEDURE GetData
3> GO
1>
2> -- Filtering for Null Values with a Stored Procedure
3>
4> CREATE PROCEDURE GetData
5> @TID int = NULL
6> AS
7> IF @TID IS NOT NULL
8> SELECT ID, name FROM Employee WHERE ID = @TID
9> ELSE
10> SELECT id, Name FROM Employee WHERE ID IS NULL
11> GO
1>
2>
3> EXEC GetData 1
4> GO
ID name
----------- ----------
1 Jason
(1 rows affected)
1> EXEC GetData NULL
2> GO
id Name
----------- ----------
(0 rows affected)
1> EXEC GetData
2> GO
id Name
----------- ----------
(0 rows affected)
1>
2> drop table employee
3> GO
1>
Specifying a Default Parameter Value
1> create table employee(
2> ID int,
3> name nvarchar (10),
4> salary int,
5> start_date datetime,
6> city nvarchar (10),
7> region char (1))
8> GO
1>
2> insert into employee (ID, name, salary, start_date, city, region)
3> values (1, "Jason", 40420, "02/01/94", "New York", "W")
4> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (2, "Robert",14420, "01/02/95", "Vancouver","N")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (3, "Celia", 24020, "12/03/96", "Toronto", "W")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (4, "Linda", 40620, "11/04/97", "New York", "N")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (5, "David", 80026, "10/05/98", "Vancouver","W")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (6, "James", 70060, "09/06/99", "Toronto", "N")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (7, "Alison",90620, "08/07/00", "New York", "W")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (8, "Chris", 26020, "07/08/01", "Vancouver","N")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (9, "Mary", 60020, "06/09/02", "Toronto", "W")
3> GO
(1 rows affected)
1>
2> select * from employee
3> GO
ID name salary start_date city region
----------- ---------- ----------- ----------------------- ---------- ------
1 Jason 40420 1994-02-01 00:00:00.000 New York W
2 Robert 14420 1995-01-02 00:00:00.000 Vancouver N
3 Celia 24020 1996-12-03 00:00:00.000 Toronto W
4 Linda 40620 1997-11-04 00:00:00.000 New York N
5 David 80026 1998-10-05 00:00:00.000 Vancouver W
6 James 70060 1999-09-06 00:00:00.000 Toronto N
7 Alison 90620 2000-08-07 00:00:00.000 New York W
8 Chris 26020 2001-07-08 00:00:00.000 Vancouver N
9 Mary 60020 2002-06-09 00:00:00.000 Toronto W
(9 rows affected)
1>
2> drop PROCEDURE GetData
3> GO
1>
2> -- Specifying a Default Parameter Value
3>
4> CREATE PROCEDURE GetData @TID int = 3
5> AS
6> SELECT * from Employee WHERE ID = @TID
7> GO
1>
2> EXEC GetData 1
3> GO
ID name salary start_date city region
----------- ---------- ----------- ----------------------- ---------- ------
1 Jason 40420 1994-02-01 00:00:00.000 New York W
(1 rows affected)
1>
2> EXEC GetData
3> GO
ID name salary start_date city region
----------- ---------- ----------- ----------------------- ---------- ------
3 Celia 24020 1996-12-03 00:00:00.000 Toronto W
(1 rows affected)
1>
2> drop table employee
3> GO
1>
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