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