Mercurial > pub > bltoolkit
comparison Data/Create Scripts/MsSql.sql @ 0:f990fcb411a9
Копия текущей версии из github
author | cin |
---|---|
date | Thu, 27 Mar 2014 21:46:09 +0400 |
parents | |
children |
comparison
equal
deleted
inserted
replaced
-1:000000000000 | 0:f990fcb411a9 |
---|---|
1 --CREATE DATABASE BLToolkitData ON PRIMARY | |
2 --(NAME=N'BLToolkitTest', FILENAME=N'C:\Data\MSSQL.1\MSSQL\DATA\BLToolkitData.mdf', SIZE=3072KB, FILEGROWTH=1024KB) | |
3 --LOG ON | |
4 --(NAME=N'BLToolkitTest_log', FILENAME=N'C:\Data\MSSQL.1\MSSQL\DATA\BLToolkitData_log.ldf', SIZE=1024KB, FILEGROWTH=10%) | |
5 --GO | |
6 | |
7 IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('Doctor') AND type in (N'U')) | |
8 BEGIN DROP TABLE Doctor END | |
9 | |
10 IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('Patient') AND type in (N'U')) | |
11 BEGIN DROP TABLE Patient END | |
12 | |
13 -- Person Table | |
14 | |
15 IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('Person') AND type in (N'U')) | |
16 BEGIN DROP TABLE Person END | |
17 | |
18 CREATE TABLE Person | |
19 ( | |
20 PersonID int NOT NULL IDENTITY(1,1) CONSTRAINT PK_Person PRIMARY KEY CLUSTERED, | |
21 FirstName nvarchar(50) NOT NULL, | |
22 LastName nvarchar(50) NOT NULL, | |
23 MiddleName nvarchar(50) NULL, | |
24 Gender char(1) NOT NULL CONSTRAINT CK_Person_Gender CHECK (Gender in ('M', 'F', 'U', 'O')) | |
25 ) | |
26 ON [PRIMARY] | |
27 GO | |
28 | |
29 INSERT INTO Person (FirstName, LastName, Gender) VALUES ('John', 'Pupkin', 'M') | |
30 GO | |
31 INSERT INTO Person (FirstName, LastName, Gender) VALUES ('Tester', 'Testerson', 'M') | |
32 GO | |
33 | |
34 -- Doctor Table Extension | |
35 | |
36 CREATE TABLE Doctor | |
37 ( | |
38 PersonID int NOT NULL | |
39 CONSTRAINT PK_Doctor PRIMARY KEY CLUSTERED | |
40 CONSTRAINT FK_Doctor_Person FOREIGN KEY | |
41 REFERENCES Person ([PersonID]) | |
42 ON UPDATE CASCADE | |
43 ON DELETE CASCADE, | |
44 Taxonomy nvarchar(50) NOT NULL | |
45 ) | |
46 ON [PRIMARY] | |
47 GO | |
48 | |
49 INSERT INTO Doctor (PersonID, Taxonomy) VALUES (1, 'Psychiatry') | |
50 GO | |
51 | |
52 -- Patient Table Extension | |
53 | |
54 CREATE TABLE Patient | |
55 ( | |
56 PersonID int NOT NULL | |
57 CONSTRAINT PK_Patient PRIMARY KEY CLUSTERED | |
58 CONSTRAINT FK_Patient_Person FOREIGN KEY | |
59 REFERENCES Person ([PersonID]) | |
60 ON UPDATE CASCADE | |
61 ON DELETE CASCADE, | |
62 Diagnosis nvarchar(256) NOT NULL | |
63 ) | |
64 ON [PRIMARY] | |
65 GO | |
66 | |
67 INSERT INTO Patient (PersonID, Diagnosis) VALUES (2, 'Hallucination with Paranoid Bugs'' Delirium of Persecution') | |
68 GO | |
69 | |
70 -- Person_SelectByKey | |
71 | |
72 IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'Person_SelectByKey') | |
73 BEGIN DROP Procedure Person_SelectByKey | |
74 END | |
75 GO | |
76 | |
77 CREATE Procedure Person_SelectByKey | |
78 @id int | |
79 AS | |
80 | |
81 SELECT * FROM Person WHERE PersonID = @id | |
82 | |
83 GO | |
84 | |
85 GRANT EXEC ON Person_SelectByKey TO PUBLIC | |
86 GO | |
87 | |
88 -- Person_SelectAll | |
89 | |
90 IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'Person_SelectAll') | |
91 BEGIN DROP Procedure Person_SelectAll END | |
92 GO | |
93 | |
94 CREATE Procedure Person_SelectAll | |
95 AS | |
96 | |
97 SELECT * FROM Person | |
98 | |
99 GO | |
100 | |
101 GRANT EXEC ON Person_SelectAll TO PUBLIC | |
102 GO | |
103 | |
104 -- Person_SelectByName | |
105 | |
106 IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'Person_SelectByName') | |
107 BEGIN DROP Procedure Person_SelectByName END | |
108 GO | |
109 | |
110 CREATE Procedure Person_SelectByName | |
111 @firstName nvarchar(50), | |
112 @lastName nvarchar(50) | |
113 AS | |
114 | |
115 SELECT | |
116 * | |
117 FROM | |
118 Person | |
119 WHERE | |
120 FirstName = @firstName AND LastName = @lastName | |
121 | |
122 GO | |
123 | |
124 GRANT EXEC ON Person_SelectByName TO PUBLIC | |
125 GO | |
126 | |
127 -- Person_SelectListByName | |
128 | |
129 IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'Person_SelectListByName') | |
130 BEGIN DROP Procedure Person_SelectListByName | |
131 END | |
132 GO | |
133 | |
134 CREATE Procedure Person_SelectListByName | |
135 @firstName nvarchar(50), | |
136 @lastName nvarchar(50) | |
137 AS | |
138 | |
139 SELECT | |
140 * | |
141 FROM | |
142 Person | |
143 WHERE | |
144 FirstName like @firstName AND LastName like @lastName | |
145 | |
146 GO | |
147 | |
148 GRANT EXEC ON Person_SelectByName TO PUBLIC | |
149 GO | |
150 | |
151 -- Person_Insert | |
152 | |
153 IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'Person_Insert') | |
154 BEGIN DROP Procedure Person_Insert END | |
155 GO | |
156 | |
157 CREATE Procedure Person_Insert | |
158 @FirstName nvarchar(50), | |
159 @LastName nvarchar(50), | |
160 @MiddleName nvarchar(50), | |
161 @Gender char(1) | |
162 AS | |
163 | |
164 INSERT INTO Person | |
165 ( LastName, FirstName, MiddleName, Gender) | |
166 VALUES | |
167 (@LastName, @FirstName, @MiddleName, @Gender) | |
168 | |
169 SELECT Cast(SCOPE_IDENTITY() as int) PersonID | |
170 | |
171 GO | |
172 | |
173 GRANT EXEC ON Person_Insert TO PUBLIC | |
174 GO | |
175 | |
176 -- Person_Insert_OutputParameter | |
177 | |
178 IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'Person_Insert_OutputParameter') | |
179 BEGIN DROP Procedure Person_Insert_OutputParameter END | |
180 GO | |
181 | |
182 CREATE Procedure Person_Insert_OutputParameter | |
183 @FirstName nvarchar(50), | |
184 @LastName nvarchar(50), | |
185 @MiddleName nvarchar(50), | |
186 @Gender char(1), | |
187 @PersonID int output | |
188 AS | |
189 | |
190 INSERT INTO Person | |
191 ( LastName, FirstName, MiddleName, Gender) | |
192 VALUES | |
193 (@LastName, @FirstName, @MiddleName, @Gender) | |
194 | |
195 SET @PersonID = Cast(SCOPE_IDENTITY() as int) | |
196 | |
197 GO | |
198 | |
199 GRANT EXEC ON Person_Insert_OutputParameter TO PUBLIC | |
200 GO | |
201 | |
202 -- Person_Update | |
203 | |
204 IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'Person_Update') | |
205 BEGIN DROP Procedure Person_Update END | |
206 GO | |
207 | |
208 CREATE Procedure Person_Update | |
209 @PersonID int, | |
210 @FirstName nvarchar(50), | |
211 @LastName nvarchar(50), | |
212 @MiddleName nvarchar(50), | |
213 @Gender char(1) | |
214 AS | |
215 | |
216 UPDATE | |
217 Person | |
218 SET | |
219 LastName = @LastName, | |
220 FirstName = @FirstName, | |
221 MiddleName = @MiddleName, | |
222 Gender = @Gender | |
223 WHERE | |
224 PersonID = @PersonID | |
225 | |
226 GO | |
227 | |
228 GRANT EXEC ON Person_Update TO PUBLIC | |
229 GO | |
230 | |
231 -- Person_Delete | |
232 | |
233 IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'Person_Delete') | |
234 BEGIN DROP Procedure Person_Delete END | |
235 GO | |
236 | |
237 CREATE Procedure Person_Delete | |
238 @PersonID int | |
239 AS | |
240 | |
241 DELETE FROM Person WHERE PersonID = @PersonID | |
242 | |
243 GO | |
244 | |
245 GRANT EXEC ON Person_Delete TO PUBLIC | |
246 GO | |
247 | |
248 -- Patient_SelectAll | |
249 | |
250 IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'Patient_SelectAll') | |
251 BEGIN DROP Procedure Patient_SelectAll END | |
252 GO | |
253 | |
254 CREATE Procedure Patient_SelectAll | |
255 AS | |
256 | |
257 SELECT | |
258 Person.*, Patient.Diagnosis | |
259 FROM | |
260 Patient, Person | |
261 WHERE | |
262 Patient.PersonID = Person.PersonID | |
263 | |
264 GO | |
265 | |
266 GRANT EXEC ON Patient_SelectAll TO PUBLIC | |
267 GO | |
268 | |
269 -- Patient_SelectByName | |
270 | |
271 IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'Patient_SelectByName') | |
272 BEGIN DROP Procedure Patient_SelectByName END | |
273 GO | |
274 | |
275 CREATE Procedure Patient_SelectByName | |
276 @firstName nvarchar(50), | |
277 @lastName nvarchar(50) | |
278 AS | |
279 | |
280 SELECT | |
281 Person.*, Patient.Diagnosis | |
282 FROM | |
283 Patient, Person | |
284 WHERE | |
285 Patient.PersonID = Person.PersonID | |
286 AND FirstName = @firstName AND LastName = @lastName | |
287 | |
288 GO | |
289 | |
290 GRANT EXEC ON Person_SelectByName TO PUBLIC | |
291 GO | |
292 | |
293 -- BinaryData Table | |
294 | |
295 IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('BinaryData') AND type in (N'U')) | |
296 BEGIN DROP TABLE BinaryData END | |
297 | |
298 CREATE TABLE BinaryData | |
299 ( | |
300 BinaryDataID int NOT NULL IDENTITY(1,1) CONSTRAINT PK_BinaryData PRIMARY KEY CLUSTERED, | |
301 Stamp timestamp NOT NULL, | |
302 Data varbinary(1024) NOT NULL) | |
303 ON [PRIMARY] | |
304 GO | |
305 | |
306 -- OutRefTest | |
307 | |
308 IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'OutRefTest') | |
309 BEGIN DROP Procedure OutRefTest END | |
310 GO | |
311 | |
312 CREATE Procedure OutRefTest | |
313 @ID int, | |
314 @outputID int output, | |
315 @inputOutputID int output, | |
316 @str varchar(50), | |
317 @outputStr varchar(50) output, | |
318 @inputOutputStr varchar(50) output | |
319 AS | |
320 | |
321 SET @outputID = @ID | |
322 SET @inputOutputID = @ID + @inputOutputID | |
323 SET @outputStr = @str | |
324 SET @inputOutputStr = @str + @inputOutputStr | |
325 | |
326 GO | |
327 | |
328 -- OutRefEnumTest | |
329 | |
330 IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'OutRefEnumTest') | |
331 BEGIN DROP Procedure OutRefEnumTest END | |
332 GO | |
333 | |
334 CREATE Procedure OutRefEnumTest | |
335 @str varchar(50), | |
336 @outputStr varchar(50) output, | |
337 @inputOutputStr varchar(50) output | |
338 AS | |
339 | |
340 SET @outputStr = @str | |
341 SET @inputOutputStr = @str + @inputOutputStr | |
342 | |
343 GO | |
344 | |
345 -- ExecuteScalarTest | |
346 | |
347 IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'Scalar_DataReader') | |
348 BEGIN DROP Procedure Scalar_DataReader END | |
349 GO | |
350 | |
351 CREATE Procedure Scalar_DataReader | |
352 AS | |
353 SELECT Cast(12345 as int) AS intField, Cast('54321' as varchar(50)) AS stringField | |
354 | |
355 GO | |
356 | |
357 IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'Scalar_OutputParameter') | |
358 BEGIN DROP Procedure Scalar_OutputParameter END | |
359 GO | |
360 | |
361 CREATE Procedure Scalar_OutputParameter | |
362 @outputInt int = 0 output, | |
363 @outputString varchar(50) = '' output | |
364 AS | |
365 BEGIN | |
366 SET @outputInt = 12345 | |
367 SET @outputString = '54321' | |
368 END | |
369 | |
370 GO | |
371 | |
372 IF EXISTS (SELECT * FROM sys.objects WHERE type in (N'FN', N'IF', N'TF', N'FS', N'FT') AND name = 'Scalar_ReturnParameter') | |
373 BEGIN DROP Function Scalar_ReturnParameter END | |
374 GO | |
375 | |
376 CREATE Function Scalar_ReturnParameter() | |
377 RETURNS int | |
378 AS | |
379 BEGIN | |
380 RETURN 12345 | |
381 END | |
382 | |
383 GO | |
384 | |
385 IF EXISTS (SELECT * FROM sys.objects WHERE type ='P' AND name = 'Scalar_ReturnParameterWithObject') | |
386 BEGIN DROP Procedure Scalar_ReturnParameterWithObject END | |
387 GO | |
388 | |
389 CREATE Procedure Scalar_ReturnParameterWithObject | |
390 @id int | |
391 AS | |
392 BEGIN | |
393 SELECT * FROM Person WHERE PersonID = @id | |
394 RETURN @id | |
395 END | |
396 | |
397 GO | |
398 | |
399 -- Data Types test | |
400 | |
401 IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('DataTypeTest') AND type in (N'U')) | |
402 BEGIN DROP TABLE DataTypeTest END | |
403 GO | |
404 | |
405 CREATE TABLE DataTypeTest | |
406 ( | |
407 DataTypeID int NOT NULL IDENTITY(1,1) CONSTRAINT PK_DataType PRIMARY KEY CLUSTERED, | |
408 Binary_ binary(50) NULL, | |
409 Boolean_ bit NULL, | |
410 Byte_ tinyint NULL, | |
411 Bytes_ varbinary(50) NULL, | |
412 Char_ char(1) NULL, | |
413 DateTime_ datetime NULL, | |
414 Decimal_ decimal(20,2) NULL, | |
415 Double_ float NULL, | |
416 Guid_ uniqueidentifier NULL, | |
417 Int16_ smallint NULL, | |
418 Int32_ int NULL, | |
419 Int64_ bigint NULL, | |
420 Money_ money NULL, | |
421 SByte_ tinyint NULL, | |
422 Single_ real NULL, | |
423 Stream_ varbinary(50) NULL, | |
424 String_ nvarchar(50) NULL, | |
425 UInt16_ smallint NULL, | |
426 UInt32_ int NULL, | |
427 UInt64_ bigint NULL, | |
428 Xml_ xml NULL | |
429 ) ON [PRIMARY] | |
430 GO | |
431 | |
432 INSERT INTO DataTypeTest | |
433 (Binary_, Boolean_, Byte_, Bytes_, Char_, DateTime_, Decimal_, | |
434 Double_, Guid_, Int16_, Int32_, Int64_, Money_, SByte_, | |
435 Single_, Stream_, String_, UInt16_, UInt32_, UInt64_, Xml_) | |
436 VALUES | |
437 ( NULL, NULL, NULL, NULL, NULL, NULL, NULL, | |
438 NULL, NULL, NULL, NULL, NULL, NULL, NULL, | |
439 NULL, NULL, NULL, NULL, NULL, NULL, NULL) | |
440 GO | |
441 | |
442 INSERT INTO DataTypeTest | |
443 (Binary_, Boolean_, Byte_, Bytes_, Char_, DateTime_, Decimal_, | |
444 Double_, Guid_, Int16_, Int32_, Int64_, Money_, SByte_, | |
445 Single_, Stream_, String_, UInt16_, UInt32_, UInt64_, | |
446 Xml_) | |
447 VALUES | |
448 (NewID(), 1, 255, NewID(), 'B', GetDate(), 12345.67, | |
449 1234.567, NewID(), 32767, 32768, 1000000, 12.3456, 127, | |
450 1234.123, NewID(), 'string', 32767, 32768, 200000000, | |
451 '<root><element strattr="strvalue" intattr="12345"/></root>') | |
452 GO | |
453 | |
454 -- SKIP Sql2005 BEGIN | |
455 -- | |
456 -- Arrays | |
457 -- | |
458 IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'ArrayTest') | |
459 BEGIN DROP PROCEDURE ArrayTest END | |
460 GO | |
461 | |
462 --IF EXISTS (SELECT * FROM sys.objects WHERE type = 'T' AND name = 'IntArray') | |
463 --BEGIN | |
464 DROP TYPE IntArray | |
465 --END | |
466 GO | |
467 | |
468 CREATE TYPE IntArray AS TABLE | |
469 ( | |
470 Num int NULL | |
471 ) | |
472 GO | |
473 | |
474 CREATE PROCEDURE ArrayTest | |
475 @InputIntArray IntArray READONLY | |
476 AS | |
477 BEGIN | |
478 SELECT Num * 2 FROM @InputIntArray; | |
479 END | |
480 GO | |
481 -- SKIP Sql2005 END | |
482 | |
483 DROP FUNCTION GetParentByID | |
484 GO | |
485 | |
486 DROP TABLE Parent | |
487 GO | |
488 DROP TABLE Child | |
489 GO | |
490 DROP TABLE GrandChild | |
491 GO | |
492 | |
493 CREATE TABLE Parent (ParentID int, Value1 int) | |
494 GO | |
495 CREATE TABLE Child (ParentID int, ChildID int) | |
496 GO | |
497 CREATE TABLE GrandChild (ParentID int, ChildID int, GrandChildID int) | |
498 GO | |
499 | |
500 CREATE FUNCTION GetParentByID(@id int) | |
501 RETURNS TABLE | |
502 AS | |
503 RETURN | |
504 ( | |
505 SELECT * FROM Parent WHERE ParentID = @id | |
506 ) | |
507 GO | |
508 | |
509 IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('LinqDataTypes') AND type in (N'U')) | |
510 BEGIN DROP TABLE LinqDataTypes END | |
511 GO | |
512 | |
513 -- SKIP Sql2005 BEGIN | |
514 CREATE TABLE LinqDataTypes | |
515 ( | |
516 ID int, | |
517 MoneyValue decimal(10,4), | |
518 DateTimeValue datetime, | |
519 DateTimeValue2 datetime2, | |
520 BoolValue bit, | |
521 GuidValue uniqueidentifier, | |
522 BinaryValue varbinary(5000), | |
523 SmallIntValue smallint, | |
524 IntValue int NULL, | |
525 BigIntValue bigint NULL | |
526 ) | |
527 GO | |
528 -- SKIP Sql2005 END | |
529 | |
530 -- SKIP Sql2008 BEGIN | |
531 -- SKIP Sql2012 BEGIN | |
532 CREATE TABLE LinqDataTypes | |
533 ( | |
534 ID int, | |
535 MoneyValue decimal(10,4), | |
536 DateTimeValue datetime, | |
537 DateTimeValue2 datetime, | |
538 BoolValue bit, | |
539 GuidValue uniqueidentifier, | |
540 BinaryValue varbinary(5000) NULL, | |
541 SmallIntValue smallint, | |
542 IntValue int NULL, | |
543 BigIntValue bigint NULL | |
544 ) | |
545 GO | |
546 -- SKIP Sql2012 END | |
547 -- SKIP Sql2008 END | |
548 | |
549 DROP TABLE TestIdentity | |
550 GO | |
551 | |
552 CREATE TABLE TestIdentity ( | |
553 ID int NOT NULL IDENTITY(1,1) CONSTRAINT PK_TestIdentity PRIMARY KEY CLUSTERED | |
554 ) | |
555 GO |