4 -- =================================== OBJECT EMPLOYEE ============================
6 -- data with id > 6000 is not gaurenteed to be read-only.
7 if exists (select name from sysobjects where
8 name = 'numeric_family' and type = 'U')
9 drop table numeric_family
12 create table numeric_family (
13 id int PRIMARY KEY NOT NULL,
14 type_bit bit NOT NULL,
15 type_tinyint tinyint NULL,
16 type_smallint smallint NULL,
18 type_bigint numeric (38,0) NULL,
19 type_decimal decimal (38, 0) NULL,
20 type_numeric numeric (38, 0) NULL,
21 type_money money NULL,
22 type_smallmoney smallmoney NULL)
24 grant all on numeric_family to monotester
27 insert into numeric_family values (1,1,255,32767,2147483647,9223372036854775807,1000,1000,922337203685477.5807,214748.3647)
28 insert into numeric_family values (2,0,0,-32768,-2147483648,-9223372036854775808,-1000,-1000,-922337203685477.5808,-214748.3648)
29 insert into numeric_family values (3,0,0,0,0,0,0,0,0,0)
30 insert into numeric_family values (4,0,null,null,null,null,null,null,null,null)
33 -- =================================== END OBJECT EMPLOYEE ========================
35 -- =================================== OBJECT BINARY_FAMILY =========================
36 -- TABLE : BINARY FAMILY
37 -- data with id > 6000 is not gaurenteed to be read-only.
38 if exists (select name from sysobjects where
39 name = 'binary_family' and type = 'U')
40 drop table binary_family
43 create table binary_family (
44 id int PRIMARY KEY NOT NULL,
45 type_binary binary NULL,
46 type_varbinary varbinary (255) NULL,
48 type_tinyblob image NULL,
49 type_mediumblob image NULL,
50 type_longblob_image image NULL)
52 grant all on binary_family to monotester
55 insert into binary_family values (1, convert (binary, '555555'), convert (varbinary, '0123456789012345678901234567890123456789012345678901234567890123456789'),
56 convert (image, '66666666'), convert (image, '777777'),
57 convert (image, '888888'), convert (image, '999999'))
58 --insert into binary_family values (2,
59 --insert into binary_family values (3,
60 insert into binary_family values (4,null,null,null,null,null,null)
63 -- =================================== END OBJECT BINARY_FAMILY ========================
66 -- =================================== OBJECT EMPLOYEE ============================
68 -- data with id above 6000 is not gaurenteed to be read-only.
69 if exists (select name from sysobjects where
70 name = 'employee' and type = 'U')
74 create table employee (
75 id int PRIMARY KEY NOT NULL,
76 fname varchar (50) NOT NULL,
77 lname varchar (50) NULL,
78 dob datetime NOT NULL,
79 doj datetime NOT NULL,
80 email varchar (50) NULL)
83 grant all privileges on employee to monotester
85 insert into employee values (1, 'suresh', 'kumar', '1978-08-22', '2001-03-12', 'suresh@gmail.com')
86 insert into employee values (2, 'ramesh', 'rajendran', '1977-02-15', '2005-02-11', 'ramesh@yahoo.com')
87 insert into employee values (3, 'venkat', 'ramakrishnan', '1977-06-12', '2003-12-11', 'ramesh@yahoo.com')
88 insert into employee values (4, 'ramu', 'dhasarath', '1977-02-15', '2005-02-11', 'ramesh@yahoo.com')
92 -- SP : sp_clean_employee_table
93 if exists (select name from sysobjects where
94 name = 'sp_clean_employee_table' and type = 'P')
95 drop procedure sp_clean_employee_table
98 create procedure sp_clean_employee_table
101 delete from employee where id > 6000
105 grant all on sp_clean_employee_table to monotester
109 if exists (select name from sysobjects where
110 name = 'sp_get_age' and type = 'P')
111 drop procedure sp_get_age
114 create procedure sp_get_age (
119 select @age = datediff (day, dob, getdate ()) from employee where fname like @fname
124 grant all on sp_get_age to monotester
128 -- =================================== END OBJECT EMPLOYEE ============================