|
0
|
1
|
|
|
2 DROP TABLE Doctor
|
|
|
3 GO
|
|
|
4 DROP TABLE Patient
|
|
|
5 GO
|
|
|
6
|
|
|
7 -- Person Table
|
|
|
8
|
|
|
9 DROP TABLE Person
|
|
|
10 GO
|
|
|
11
|
|
|
12 CREATE TABLE Person
|
|
|
13 (
|
|
|
14 PersonID int AUTO_INCREMENT NOT NULL,
|
|
|
15 FirstName varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
|
|
|
16 LastName varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
|
|
|
17 MiddleName varchar(50) NULL,
|
|
|
18 Gender char(1) NOT NULL,
|
|
|
19 CONSTRAINT PK_Person PRIMARY KEY CLUSTERED (PersonID)
|
|
|
20 )
|
|
|
21 GO
|
|
|
22
|
|
|
23 INSERT INTO Person (FirstName, LastName, Gender) VALUES ('John', 'Pupkin', 'M')
|
|
|
24 GO
|
|
|
25 INSERT INTO Person (FirstName, LastName, Gender) VALUES ('Tester', 'Testerson', 'M')
|
|
|
26 GO
|
|
|
27
|
|
|
28 -- Doctor Table Extension
|
|
|
29
|
|
|
30 CREATE TABLE Doctor
|
|
|
31 (
|
|
|
32 PersonID int NOT NULL,
|
|
|
33 Taxonomy varchar(50) NOT NULL,
|
|
|
34 CONSTRAINT PK_Doctor PRIMARY KEY CLUSTERED (PersonID),
|
|
|
35 CONSTRAINT FK_Doctor_Person FOREIGN KEY (PersonID)
|
|
|
36 REFERENCES Person(PersonID)
|
|
|
37 )
|
|
|
38 GO
|
|
|
39
|
|
|
40 INSERT INTO Doctor (PersonID, Taxonomy) VALUES (1, 'Psychiatry')
|
|
|
41 GO
|
|
|
42
|
|
|
43 -- Patient Table Extension
|
|
|
44
|
|
|
45 CREATE TABLE Patient
|
|
|
46 (
|
|
|
47 PersonID int NOT NULL,
|
|
|
48 Diagnosis varchar(256) NOT NULL,
|
|
|
49 CONSTRAINT PK_Patient PRIMARY KEY CLUSTERED (PersonID),
|
|
|
50 CONSTRAINT FK_Patient_Person FOREIGN KEY (PersonID)
|
|
|
51 REFERENCES Person (PersonID)
|
|
|
52 )
|
|
|
53 GO
|
|
|
54
|
|
|
55 INSERT INTO Patient (PersonID, Diagnosis) VALUES (2, 'Hallucination with Paranoid Bugs'' Delirium of Persecution')
|
|
|
56 GO
|
|
|
57
|
|
|
58
|
|
|
59 -- GetPersonById
|
|
|
60
|
|
|
61 DROP Procedure GetPersonById
|
|
|
62 GO
|
|
|
63
|
|
|
64 CREATE Procedure GetPersonById(_ID INT)
|
|
|
65 BEGIN
|
|
|
66
|
|
|
67 SELECT * FROM Person WHERE PersonID = _ID;
|
|
|
68
|
|
|
69 END
|
|
|
70 GO
|
|
|
71
|
|
|
72 -- GetPersonByName
|
|
|
73
|
|
|
74 DROP Procedure GetPersonByName
|
|
|
75 GO
|
|
|
76
|
|
|
77 CREATE Procedure GetPersonByName
|
|
|
78 (
|
|
|
79 _firstName varchar(50),
|
|
|
80 _lastName varchar(50)
|
|
|
81 )
|
|
|
82 BEGIN
|
|
|
83
|
|
|
84 SELECT * FROM Person WHERE FirstName = _firstName AND LastName = _lastName;
|
|
|
85
|
|
|
86 END
|
|
|
87 GO
|
|
|
88
|
|
|
89 -- Person_SelectByKey
|
|
|
90
|
|
|
91 DROP Procedure Person_SelectByKey
|
|
|
92 GO
|
|
|
93
|
|
|
94 CREATE Procedure Person_SelectByKey(id int)
|
|
|
95 BEGIN
|
|
|
96
|
|
|
97 SELECT * FROM Person WHERE PersonID = id;
|
|
|
98
|
|
|
99 END
|
|
|
100 GO
|
|
|
101
|
|
|
102 -- Person_SelectAll
|
|
|
103
|
|
|
104 DROP Procedure Person_SelectAll
|
|
|
105 GO
|
|
|
106
|
|
|
107 CREATE Procedure Person_SelectAll()
|
|
|
108 BEGIN
|
|
|
109
|
|
|
110 SELECT * FROM Person;
|
|
|
111
|
|
|
112 END
|
|
|
113 GO
|
|
|
114
|
|
|
115 -- Person_SelectByName
|
|
|
116
|
|
|
117 DROP Procedure Person_SelectByName
|
|
|
118 GO
|
|
|
119
|
|
|
120 CREATE Procedure Person_SelectByName
|
|
|
121 (
|
|
|
122 firstName varchar(50),
|
|
|
123 lastName varchar(50)
|
|
|
124 )
|
|
|
125 BEGIN
|
|
|
126
|
|
|
127 SELECT
|
|
|
128 *
|
|
|
129 FROM
|
|
|
130 Person
|
|
|
131 WHERE
|
|
|
132 FirstName = firstName AND LastName = lastName;
|
|
|
133
|
|
|
134 END
|
|
|
135 GO
|
|
|
136
|
|
|
137 -- Person_SelectListByName
|
|
|
138
|
|
|
139 DROP Procedure Person_SelectListByName
|
|
|
140 GO
|
|
|
141
|
|
|
142 CREATE Procedure Person_SelectListByName
|
|
|
143 (
|
|
|
144 firstName varchar(50),
|
|
|
145 lastName varchar(50)
|
|
|
146 )
|
|
|
147 BEGIN
|
|
|
148
|
|
|
149 SELECT
|
|
|
150 *
|
|
|
151 FROM
|
|
|
152 Person
|
|
|
153 WHERE
|
|
|
154 FirstName like firstName AND LastName like lastName;
|
|
|
155
|
|
|
156 END
|
|
|
157 GO
|
|
|
158
|
|
|
159 -- Person_Insert
|
|
|
160
|
|
|
161 DROP Procedure Person_Insert
|
|
|
162 GO
|
|
|
163
|
|
|
164 CREATE Procedure Person_Insert
|
|
|
165 (
|
|
|
166 FirstName varchar(50),
|
|
|
167 LastName varchar(50),
|
|
|
168 MiddleName varchar(50),
|
|
|
169 Gender char(1)
|
|
|
170 )
|
|
|
171 BEGIN
|
|
|
172
|
|
|
173 INSERT INTO Person
|
|
|
174 (LastName, FirstName, MiddleName, Gender)
|
|
|
175 VALUES
|
|
|
176 (LastName, FirstName, MiddleName, Gender);
|
|
|
177
|
|
|
178 SELECT LAST_INSERT_ID() AS PersonID;
|
|
|
179
|
|
|
180 END
|
|
|
181 GO
|
|
|
182
|
|
|
183 -- Person_Insert_OutputParameter
|
|
|
184
|
|
|
185 DROP Procedure Person_Insert_OutputParameter
|
|
|
186 GO
|
|
|
187
|
|
|
188 CREATE Procedure Person_Insert_OutputParameter
|
|
|
189 (
|
|
|
190 FirstName varchar(50),
|
|
|
191 LastName varchar(50),
|
|
|
192 MiddleName varchar(50),
|
|
|
193 Gender char(1),
|
|
|
194 OUT PersonID int
|
|
|
195 )
|
|
|
196 BEGIN
|
|
|
197
|
|
|
198 INSERT INTO Person
|
|
|
199 (LastName, FirstName, MiddleName, Gender)
|
|
|
200 VALUES
|
|
|
201 (LastName, FirstName, MiddleName, Gender);
|
|
|
202
|
|
|
203 SET PersonID = LAST_INSERT_ID();
|
|
|
204
|
|
|
205 END
|
|
|
206 GO
|
|
|
207
|
|
|
208 -- Person_Update
|
|
|
209
|
|
|
210 DROP Procedure Person_Update
|
|
|
211 GO
|
|
|
212
|
|
|
213 CREATE Procedure Person_Update
|
|
|
214 (
|
|
|
215 PersonID int,
|
|
|
216 FirstName varchar(50),
|
|
|
217 LastName varchar(50),
|
|
|
218 MiddleName varchar(50),
|
|
|
219 Gender char(1)
|
|
|
220 )
|
|
|
221 BEGIN
|
|
|
222
|
|
|
223 UPDATE
|
|
|
224 Person
|
|
|
225 SET
|
|
|
226 LastName = LastName,
|
|
|
227 FirstName = FirstName,
|
|
|
228 MiddleName = MiddleName,
|
|
|
229 Gender = Gender
|
|
|
230 WHERE
|
|
|
231 PersonID = PersonID;
|
|
|
232
|
|
|
233 END
|
|
|
234 GO
|
|
|
235
|
|
|
236 -- Person_Delete
|
|
|
237
|
|
|
238 DROP Procedure Person_Delete
|
|
|
239 GO
|
|
|
240
|
|
|
241 CREATE Procedure Person_Delete
|
|
|
242 (
|
|
|
243 PersonID int
|
|
|
244 )
|
|
|
245 BEGIN
|
|
|
246
|
|
|
247 DELETE FROM Person WHERE PersonID = PersonID;
|
|
|
248
|
|
|
249 END
|
|
|
250 GO
|
|
|
251
|
|
|
252 -- Patient_SelectAll
|
|
|
253
|
|
|
254 DROP Procedure Patient_SelectAll
|
|
|
255 GO
|
|
|
256
|
|
|
257 CREATE Procedure Patient_SelectAll()
|
|
|
258 BEGIN
|
|
|
259
|
|
|
260 SELECT
|
|
|
261 Person.*, Patient.Diagnosis
|
|
|
262 FROM
|
|
|
263 Patient, Person
|
|
|
264 WHERE
|
|
|
265 Patient.PersonID = Person.PersonID;
|
|
|
266
|
|
|
267 END
|
|
|
268 GO
|
|
|
269
|
|
|
270 -- Patient_SelectByName
|
|
|
271
|
|
|
272 DROP Procedure Patient_SelectByName
|
|
|
273 GO
|
|
|
274
|
|
|
275 CREATE Procedure Patient_SelectByName
|
|
|
276 (
|
|
|
277 firstName varchar(50),
|
|
|
278 lastName varchar(50)
|
|
|
279 )
|
|
|
280 BEGIN
|
|
|
281
|
|
|
282 SELECT
|
|
|
283 Person.*, Patient.Diagnosis
|
|
|
284 FROM
|
|
|
285 Patient, Person
|
|
|
286 WHERE
|
|
|
287 Patient.PersonID = Person.PersonID
|
|
|
288 AND FirstName = firstName AND LastName = lastName;
|
|
|
289
|
|
|
290 END
|
|
|
291 GO
|
|
|
292
|
|
|
293 -- BinaryData Table
|
|
|
294
|
|
|
295 DROP TABLE BinaryData
|
|
|
296 GO
|
|
|
297
|
|
|
298 CREATE TABLE BinaryData
|
|
|
299 (
|
|
|
300 BinaryDataID int AUTO_INCREMENT NOT NULL,
|
|
|
301 Stamp timestamp NOT NULL,
|
|
|
302 Data varbinary(1024) NOT NULL,
|
|
|
303 CONSTRAINT PK_BinaryData PRIMARY KEY CLUSTERED (BinaryDataID)
|
|
|
304 )
|
|
|
305 GO
|
|
|
306
|
|
|
307 -- OutRefTest
|
|
|
308
|
|
|
309 DROP Procedure OutRefTest
|
|
|
310 GO
|
|
|
311
|
|
|
312 CREATE Procedure OutRefTest
|
|
|
313 (
|
|
|
314 ID int,
|
|
|
315 OUT outputID int,
|
|
|
316 OUT inputOutputID int,
|
|
|
317 str varchar(50),
|
|
|
318 OUT outputStr varchar(50),
|
|
|
319 OUT inputOutputStr varchar(50)
|
|
|
320 )
|
|
|
321 BEGIN
|
|
|
322
|
|
|
323 SET outputID = ID;
|
|
|
324 SET inputOutputID = ID + inputOutputID;
|
|
|
325 SET outputStr = str;
|
|
|
326 SET inputOutputStr = str + inputOutputStr;
|
|
|
327
|
|
|
328 END
|
|
|
329 GO
|
|
|
330
|
|
|
331 -- OutRefEnumTest
|
|
|
332
|
|
|
333 DROP Procedure OutRefEnumTest
|
|
|
334 GO
|
|
|
335
|
|
|
336 CREATE Procedure OutRefEnumTest
|
|
|
337 (
|
|
|
338 str varchar(50),
|
|
|
339 OUT outputStr varchar(50),
|
|
|
340 OUT inputOutputStr varchar(50)
|
|
|
341 )
|
|
|
342 BEGIN
|
|
|
343
|
|
|
344 SET outputStr = str;
|
|
|
345 SET inputOutputStr = str + inputOutputStr;
|
|
|
346
|
|
|
347 END
|
|
|
348 GO
|
|
|
349
|
|
|
350 -- ExecuteScalarTest
|
|
|
351
|
|
|
352 DROP Procedure Scalar_DataReader
|
|
|
353 GO
|
|
|
354
|
|
|
355 CREATE Procedure Scalar_DataReader()
|
|
|
356 BEGIN
|
|
|
357
|
|
|
358 SELECT
|
|
|
359 12345 AS intField,
|
|
|
360 '54321' AS stringField;
|
|
|
361
|
|
|
362 END
|
|
|
363 GO
|
|
|
364
|
|
|
365 DROP Procedure Scalar_OutputParameter
|
|
|
366 GO
|
|
|
367
|
|
|
368 CREATE Procedure Scalar_OutputParameter
|
|
|
369 (
|
|
|
370 OUT outputInt int,
|
|
|
371 OUT outputString varchar(50)
|
|
|
372 )
|
|
|
373 BEGIN
|
|
|
374
|
|
|
375 SET outputInt = 12345;
|
|
|
376 SET outputString = '54321';
|
|
|
377
|
|
|
378 END
|
|
|
379 GO
|
|
|
380
|
|
|
381 -- Data Types test
|
|
|
382
|
|
|
383 DROP TABLE DataTypeTest
|
|
|
384 GO
|
|
|
385
|
|
|
386 CREATE TABLE DataTypeTest
|
|
|
387 (
|
|
|
388 DataTypeID int AUTO_INCREMENT NOT NULL,
|
|
|
389 Binary_ binary(50) NULL,
|
|
|
390 Boolean_ bit NOT NULL,
|
|
|
391 Byte_ tinyint NULL,
|
|
|
392 Bytes_ varbinary(50) NULL,
|
|
|
393 Char_ char(1) NULL,
|
|
|
394 DateTime_ datetime NULL,
|
|
|
395 Decimal_ decimal(20,2) NULL,
|
|
|
396 Double_ float NULL,
|
|
|
397 Guid_ varbinary(50) NULL,
|
|
|
398 Int16_ smallint NULL,
|
|
|
399 Int32_ int NULL,
|
|
|
400 Int64_ bigint NULL,
|
|
|
401 Money_ decimal(20,4) NULL,
|
|
|
402 SByte_ tinyint NULL,
|
|
|
403 Single_ real NULL,
|
|
|
404 Stream_ varbinary(50) NULL,
|
|
|
405 String_ varchar(50) NULL,
|
|
|
406 UInt16_ smallint NULL,
|
|
|
407 UInt32_ int NULL,
|
|
|
408 UInt64_ bigint NULL,
|
|
|
409 Xml_ varchar(1000) NULL,
|
|
|
410 CONSTRAINT PK_DataType PRIMARY KEY CLUSTERED (DataTypeID)
|
|
|
411 )
|
|
|
412 GO
|
|
|
413
|
|
|
414 INSERT INTO DataTypeTest
|
|
|
415 (Binary_, Boolean_, Byte_, Bytes_, Char_, DateTime_, Decimal_,
|
|
|
416 Double_, Guid_, Int16_, Int32_, Int64_, Money_, SByte_,
|
|
|
417 Single_, Stream_, String_, UInt16_, UInt32_, UInt64_, Xml_)
|
|
|
418 VALUES
|
|
|
419 ( NULL, 0, NULL, NULL, NULL, NULL, NULL,
|
|
|
420 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
|
|
|
421 NULL, NULL, NULL, NULL, NULL, NULL, NULL)
|
|
|
422 GO
|
|
|
423
|
|
|
424 INSERT INTO DataTypeTest
|
|
|
425 (Binary_, Boolean_, Byte_, Bytes_, Char_, DateTime_, Decimal_,
|
|
|
426 Double_, Guid_, Int16_, Int32_, Int64_, Money_, SByte_,
|
|
|
427 Single_, Stream_, String_, UInt16_, UInt32_, UInt64_,
|
|
|
428 Xml_)
|
|
|
429 VALUES
|
|
|
430 ( UUID(), 1, 127, UUID(), 'B', CurDate(), 12345.67,
|
|
|
431 1234.567, UUID(), 32767, 32768, 1000000, 12.3456, 127,
|
|
|
432 1234.123, UUID(), 'string', 32767, 32768, 200000000,
|
|
|
433 '<root><element strattr="strvalue" intattr="12345"/></root>')
|
|
|
434 GO
|
|
|
435
|
|
|
436
|
|
|
437
|
|
|
438 DROP TABLE Parent
|
|
|
439 GO
|
|
|
440 DROP TABLE Child
|
|
|
441 GO
|
|
|
442 DROP TABLE GrandChild
|
|
|
443 GO
|
|
|
444
|
|
|
445 CREATE TABLE Parent (ParentID int, Value1 int)
|
|
|
446 GO
|
|
|
447 CREATE TABLE Child (ParentID int, ChildID int)
|
|
|
448 GO
|
|
|
449 CREATE TABLE GrandChild (ParentID int, ChildID int, GrandChildID int)
|
|
|
450 GO
|
|
|
451
|
|
|
452
|
|
|
453 DROP TABLE LinqDataTypes
|
|
|
454 GO
|
|
|
455
|
|
|
456 CREATE TABLE LinqDataTypes
|
|
|
457 (
|
|
|
458 ID int,
|
|
|
459 MoneyValue decimal(10,4),
|
|
|
460 DateTimeValue datetime,
|
|
|
461 DateTimeValue2 datetime NULL,
|
|
|
462 BoolValue boolean,
|
|
|
463 GuidValue char(36),
|
|
|
464 BinaryValue varbinary(5000) NULL,
|
|
|
465 SmallIntValue smallint,
|
|
|
466 IntValue int NULL,
|
|
|
467 BigIntValue bigint NULL
|
|
|
468 )
|
|
|
469 GO
|
|
|
470
|
|
|
471 DROP TABLE TestIdentity
|
|
|
472 GO
|
|
|
473
|
|
|
474 CREATE TABLE TestIdentity (
|
|
|
475 ID int AUTO_INCREMENT NOT NULL,
|
|
|
476 CONSTRAINT PK_TestIdentity PRIMARY KEY CLUSTERED (ID)
|
|
|
477 )
|
|
|
478 GO
|
