Mercurial > pub > bltoolkit

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