Mercurial > pub > bltoolkit

comparison Data/Create Scripts/MsSql2000.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 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