1 -- =================================== OBJECT NUMERIC_FAMILY============================
2 -- TABLE : NUMERIC_FAMILY
3 -- data with id > 6000 is not gaurenteed to be read-only.
4 if exists (select name from sysobjects where
5 name = 'numeric_family' and type = 'U')
6 drop table numeric_family;
9 create table numeric_family (
10 id int PRIMARY KEY NOT NULL,
12 type_tinyint tinyint NULL,
13 type_smallint smallint NULL,
15 type_bigint bigint NULL,
16 type_decimal1 decimal(38,0) NULL,
17 type_decimal2 decimal(10,3) NULL,
18 type_numeric1 numeric(38,0) NULL,
19 type_numeric2 numeric(10,3) NULL,
20 type_money money NULL,
21 type_smallmoney smallmoney NULL,
23 type_double float NULL,
24 type_autoincrement int identity (2, 3));
28 insert into numeric_family (id, type_bit, type_tinyint, type_smallint, type_int, type_bigint, type_decimal1, type_decimal2, type_numeric1, type_numeric2, type_money, type_smallmoney, type_float, type_double)
29 values (1, 1, 255, 32767, 2147483647, 9223372036854775807, 1000, 4456.432, 1000, 4456.432, 922337203685477.5807, 214748.3647, 3.40E+38, 1.79E+308);
30 insert into numeric_family (id, type_bit, type_tinyint, type_smallint, type_int, type_bigint, type_decimal1, type_decimal2, type_numeric1, type_numeric2, type_money, type_smallmoney, type_float, type_double)
31 values (2, 0, 0, -32768, -2147483648, -9223372036854775808, -1000, -4456.432, -1000, -4456.432, -922337203685477.5808, -214748.3648, -3.40E+38, -1.79E+308);
32 insert into numeric_family (id, type_bit, type_tinyint, type_smallint, type_int, type_bigint, type_decimal1, type_decimal2, type_numeric1, type_numeric2, type_money, type_smallmoney, type_float, type_double)
33 values (3, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0);
34 insert into numeric_family (id, type_bit, type_tinyint, type_smallint, type_int, type_bigint, type_decimal1, type_decimal2, type_numeric1, type_numeric2, type_money, type_smallmoney, type_float, type_double)
35 values (4, null, null, null, null, null, null, null, null, null, null, null, null, null);
37 -- =================================== END OBJECT NUMERIC_FAMILY ========================
39 -- =================================== OBJECT BINARY_FAMILY =========================
40 -- TABLE : BINARY_FAMILY
41 -- data with id > 6000 is not gaurenteed to be read-only.
42 if exists (select name from sysobjects where
43 name = 'binary_family' and type = 'U')
44 drop table binary_family;
47 create table binary_family (
48 id int PRIMARY KEY NOT NULL,
49 type_binary binary (8) NULL,
50 type_varbinary varbinary (255) NULL,
52 type_tinyblob image NULL,
53 type_mediumblob image NULL,
54 type_longblob_image image NULL,
55 type_timestamp timestamp NULL);
58 insert into binary_family (id, type_binary, type_varbinary, type_blob, type_tinyblob, type_mediumblob, type_longblob_image) values (
60 convert (binary, '5'),
61 convert (varbinary(255), 0x303132333435363738393031323334353637383930313233343536373839004453),
62 convert (image, 0x3256004422),
63 convert (image, 0x3A56004422),
64 convert (image, 0x2B87002233),
65 convert (image, 0x4D84002332)
67 insert into binary_family (id, type_binary, type_varbinary, type_blob, type_tinyblob, type_mediumblob, type_longblob_image) values (
69 convert (binary, 0x0033340033303531),
70 convert (varbinary, 0x003938373635003332313031323334),
71 convert (image, 0x0066066697006606669706660666970666066697066606669706660666970666066697066606669706660666970666066697066606669706660666970666066697066606669706660666970666066697066606669706660666970666066697066606669706660666970666066697066606669706660666970666066697066606669706660666970666066697066606669706660666970666066697066606669706660666970666066697066606669706660666970666066697066606669706660666970666066697066606669706660666970666066697066606669706660666970666066697066606669706660666970666066697066606669706660666970666066697066606669706660666970666066698),
72 convert (image, 0x0056334422),
73 convert (image, 0x0087342233),
74 convert (image, 0x0084352332)
76 insert into binary_family (id, type_binary, type_varbinary, type_blob, type_tinyblob, type_mediumblob, type_longblob_image) values (
79 convert (varbinary, ''),
85 insert into binary_family (id, type_binary, type_varbinary, type_blob, type_tinyblob, type_mediumblob, type_longblob_image) values (
86 4,null,null,null,null,null,null);
89 -- =================================== END OBJECT BINARY_FAMILY ========================
92 -- =================================== OBJECT STRING_FAMILY============================
93 -- TABLE : string_family
94 -- data with id above 6000 is not gaurenteed to be read-only.
95 if exists (select name from sysobjects where
96 name = 'string_family' and type = 'U')
97 drop table string_family;
100 create table string_family (
101 id int PRIMARY KEY NOT NULL,
102 type_guid uniqueidentifier NULL,
103 type_char char(10) NULL,
104 type_nchar nchar(10) NULL,
105 type_varchar varchar(10) NULL,
106 type_nvarchar nvarchar(10) NULL,
108 type_ntext ntext NULL);
111 insert into string_family values (1, 'd222a130-6383-4d36-ac5e-4e6b2591aabf', 'char', N'nchभाr', 'varchar', N'nvभारतr', 'text', N'ntभाxt');
112 insert into string_family values (2, '1c47dd1d-891b-47e8-aac8-f36608b31bc5', '0123456789', '0123456789', 'varchar ', N'nvभारतr ', 'longtext longtext longtext longtext longtext longtext longtext longtext longtext longtext longtext longtext longtext longtext longtext longtext longtext longtext longtext longtext longtext longtext longtext longtext longtext longtext longtext longtext longtext longtext ', N'ntभाxt ');
113 insert into string_family values (3, '3c47dd1d-891b-47e8-aac8-f36608b31bc5', '', '', '', '', '', '');
114 insert into string_family values (4, null, null, null, null, null, null, null);
116 -- =================================== END OBJECT STRING_FAMILY ========================
119 -- =================================== OBJECT DATETIME_FAMILY============================
120 -- TABLE : datetime_family
121 -- data with id above 6000 is not gaurenteed to be read-only.
123 if exists (select name from sysobjects where
124 name = 'datetime_family' and type = 'U')
125 drop table datetime_family;
128 create table datetime_family (
129 id int PRIMARY KEY NOT NULL,
130 type_smalldatetime smalldatetime NULL,
131 type_datetime datetime NULL);
134 insert into datetime_family values (1,'2037-12-31 23:59:00','9999-12-31 23:59:59:997');
135 insert into datetime_family values (4,null,null);
138 -- =================================== END OBJECT DATETIME_FAMILY========================
140 -- =================================== OBJECT EMPLOYEE ============================
142 -- data with id above 6000 is not gaurenteed to be read-only.
143 if exists (select name from sysobjects where
144 name = 'employee' and type = 'U')
148 create table employee (
149 id int PRIMARY KEY NOT NULL,
150 fname varchar (50) NOT NULL,
151 lname varchar (50) NULL,
152 dob datetime NOT NULL,
153 doj datetime NOT NULL,
154 email varchar (50) NULL);
157 insert into employee values (1, 'suresh', 'kumar', '1978-08-22', '2001-03-12', 'suresh@gmail.com');
158 insert into employee values (2, 'ramesh', 'rajendran', '1977-02-15', '2005-02-11', 'ramesh@yahoo.com');
159 insert into employee values (3, 'venkat', 'ramakrishnan', '1977-06-12', '2003-12-11', 'ramesh@yahoo.com');
160 insert into employee values (4, 'ramu', 'dhasarath', '1977-02-15', '2005-02-11', 'ramesh@yahoo.com');
166 -- SP : sp_clean_employee_table
167 if exists (select name from sysobjects where
168 name = 'sp_clean_employee_table' and type = 'P')
169 drop procedure sp_clean_employee_table;
172 create procedure sp_clean_employee_table
175 delete from employee where id > 6000;
180 if exists (select name from sysobjects where
181 name = 'sp_get_age' and type = 'P')
182 drop procedure sp_get_age;
185 create procedure sp_get_age (
190 select @age = datediff (day, dob, getdate ()) from employee where fname like @fname;
195 -- =================================== END OBJECT EMPLOYEE ============================
198 if exists (select name from sysobjects where
199 name = 'sp_326182a' and type = 'P')
200 drop procedure sp_326182a;
203 CREATE procedure sp_326182a (
220 if exists (select name from sysobjects where
221 name = 'sp_326182b' and type = 'P')
222 drop procedure sp_326182b;
225 CREATE procedure sp_326182b (
227 @param1 decimal (5, 2) out,
228 @param2 varchar (12))
231 set @param1 = (@param0 + @param1 + 2)