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