Mercurial > pub > bltoolkit

comparison Data/Create Scripts/MsSql.sql @ 0:f990fcb411a9

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Копия текущей версии из 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