2 CREATE DATABASE [GHTDB] ON (NAME = N'GHTDB_dat', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\GHTDB.mdf' , SIZE = 6, FILEGROWTH = 10%) LOG ON (NAME = N'GHTDB_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\GHTDB.ldf' , SIZE = 2, FILEGROWTH = 10%)
\r
3 COLLATE SQL_Latin1_General_CP1_CI_AS
\r
6 exec sp_dboption N'GHTDB', N'autoclose', N'false'
\r
9 exec sp_dboption N'GHTDB', N'bulkcopy', N'true'
\r
12 exec sp_dboption N'GHTDB', N'trunc. log', N'true'
\r
15 exec sp_dboption N'GHTDB', N'torn page detection', N'true'
\r
18 exec sp_dboption N'GHTDB', N'read only', N'false'
\r
21 exec sp_dboption N'GHTDB', N'dbo use', N'false'
\r
24 exec sp_dboption N'GHTDB', N'single', N'false'
\r
27 exec sp_dboption N'GHTDB', N'autoshrink', N'false'
\r
30 exec sp_dboption N'GHTDB', N'ANSI null default', N'false'
\r
33 exec sp_dboption N'GHTDB', N'recursive triggers', N'false'
\r
36 exec sp_dboption N'GHTDB', N'ANSI nulls', N'false'
\r
39 exec sp_dboption N'GHTDB', N'concat null yields null', N'false'
\r
42 exec sp_dboption N'GHTDB', N'cursor close on commit', N'false'
\r
45 exec sp_dboption N'GHTDB', N'default to local cursor', N'false'
\r
48 exec sp_dboption N'GHTDB', N'quoted identifier', N'false'
\r
51 exec sp_dboption N'GHTDB', N'ANSI warnings', N'false'
\r
54 exec sp_dboption N'GHTDB', N'auto create statistics', N'true'
\r
57 exec sp_dboption N'GHTDB', N'auto update statistics', N'true'
\r
60 if( ( (@@microsoftversion / power(2, 24) = 8) and (@@microsoftversion & 0xffff >= 724) ) or ( (@@microsoftversion / power(2, 24) = 7) and (@@microsoftversion & 0xffff >= 1082) ) )
\r
61 exec sp_dboption N'GHTDB', N'db chaining', N'false'
\r
67 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Products_Categories]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
\r
68 ALTER TABLE [dbo].[Products] DROP CONSTRAINT FK_Products_Categories
\r
71 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_CustomerCustomerDemo]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
\r
72 ALTER TABLE [dbo].[CustomerCustomerDemo] DROP CONSTRAINT FK_CustomerCustomerDemo
\r
75 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_CustomerCustomerDemo_Customers]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
\r
76 ALTER TABLE [dbo].[CustomerCustomerDemo] DROP CONSTRAINT FK_CustomerCustomerDemo_Customers
\r
79 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Orders_Customers]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
\r
80 ALTER TABLE [dbo].[Orders] DROP CONSTRAINT FK_Orders_Customers
\r
83 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Employees_Employees]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
\r
84 ALTER TABLE [dbo].[Employees] DROP CONSTRAINT FK_Employees_Employees
\r
87 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_EmployeeTerritories_Employees]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
\r
88 ALTER TABLE [dbo].[EmployeeTerritories] DROP CONSTRAINT FK_EmployeeTerritories_Employees
\r
91 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Orders_Employees]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
\r
92 ALTER TABLE [dbo].[Orders] DROP CONSTRAINT FK_Orders_Employees
\r
95 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Order_Details_Orders]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
\r
96 ALTER TABLE [dbo].[Order Details] DROP CONSTRAINT FK_Order_Details_Orders
\r
99 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Order_Details_Products]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
\r
100 ALTER TABLE [dbo].[Order Details] DROP CONSTRAINT FK_Order_Details_Products
\r
103 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Territories_Region]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
\r
104 ALTER TABLE [dbo].[Territories] DROP CONSTRAINT FK_Territories_Region
\r
107 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Orders_Shippers]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
\r
108 ALTER TABLE [dbo].[Orders] DROP CONSTRAINT FK_Orders_Shippers
\r
111 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Products_Suppliers]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
\r
112 ALTER TABLE [dbo].[Products] DROP CONSTRAINT FK_Products_Suppliers
\r
115 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_EmployeeTerritories_Territories]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
\r
116 ALTER TABLE [dbo].[EmployeeTerritories] DROP CONSTRAINT FK_EmployeeTerritories_Territories
\r
119 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CustOrderHist]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
\r
120 drop procedure [dbo].[CustOrderHist]
\r
123 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CustOrdersDetail]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
\r
124 drop procedure [dbo].[CustOrdersDetail]
\r
127 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CustOrdersOrders]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
\r
128 drop procedure [dbo].[CustOrdersOrders]
\r
131 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Employee Sales by Country]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
\r
132 drop procedure [dbo].[Employee Sales by Country]
\r
135 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GH_CreateTable]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
\r
136 drop procedure [dbo].[GH_CreateTable]
\r
139 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GH_MultiRecordSets]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
\r
140 drop procedure [dbo].[GH_MultiRecordSets]
\r
143 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GH_INOUT1]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
\r
144 drop procedure [dbo].[GH_INOUT1]
\r
147 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GH_REFCURSOR1]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
\r
148 drop procedure [dbo].[GH_REFCURSOR1]
\r
151 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GH_REFCURSOR2]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
\r
152 drop procedure [dbo].[GH_REFCURSOR2]
\r
155 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GH_REFCURSOR3]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
\r
156 drop procedure [dbo].[GH_REFCURSOR3]
\r
160 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sales by Year]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
\r
161 drop procedure [dbo].[Sales by Year]
\r
164 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SalesByCategory]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
\r
165 drop procedure [dbo].[SalesByCategory]
\r
168 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Ten Most Expensive Products]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
\r
169 drop procedure [dbo].[Ten Most Expensive Products]
\r
172 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Category Sales for 1997]') and OBJECTPROPERTY(id, N'IsView') = 1)
\r
173 drop view [dbo].[Category Sales for 1997]
\r
176 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sales Totals by Amount]') and OBJECTPROPERTY(id, N'IsView') = 1)
\r
177 drop view [dbo].[Sales Totals by Amount]
\r
180 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sales by Category]') and OBJECTPROPERTY(id, N'IsView') = 1)
\r
181 drop view [dbo].[Sales by Category]
\r
184 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Summary of Sales by Quarter]') and OBJECTPROPERTY(id, N'IsView') = 1)
\r
185 drop view [dbo].[Summary of Sales by Quarter]
\r
188 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Summary of Sales by Year]') and OBJECTPROPERTY(id, N'IsView') = 1)
\r
189 drop view [dbo].[Summary of Sales by Year]
\r
192 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Invoices]') and OBJECTPROPERTY(id, N'IsView') = 1)
\r
193 drop view [dbo].[Invoices]
\r
196 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Order Details Extended]') and OBJECTPROPERTY(id, N'IsView') = 1)
\r
197 drop view [dbo].[Order Details Extended]
\r
200 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Order Subtotals]') and OBJECTPROPERTY(id, N'IsView') = 1)
\r
201 drop view [dbo].[Order Subtotals]
\r
204 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Product Sales for 1997]') and OBJECTPROPERTY(id, N'IsView') = 1)
\r
205 drop view [dbo].[Product Sales for 1997]
\r
208 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Alphabetical list of products]') and OBJECTPROPERTY(id, N'IsView') = 1)
\r
209 drop view [dbo].[Alphabetical list of products]
\r
212 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Current Product List]') and OBJECTPROPERTY(id, N'IsView') = 1)
\r
213 drop view [dbo].[Current Product List]
\r
216 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Orders Qry]') and OBJECTPROPERTY(id, N'IsView') = 1)
\r
217 drop view [dbo].[Orders Qry]
\r
220 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Products Above Average Price]') and OBJECTPROPERTY(id, N'IsView') = 1)
\r
221 drop view [dbo].[Products Above Average Price]
\r
224 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Products by Category]') and OBJECTPROPERTY(id, N'IsView') = 1)
\r
225 drop view [dbo].[Products by Category]
\r
228 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Quarterly Orders]') and OBJECTPROPERTY(id, N'IsView') = 1)
\r
229 drop view [dbo].[Quarterly Orders]
\r
232 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Categories]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
\r
233 drop table [dbo].[Categories]
\r
236 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CustomerCustomerDemo]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
\r
237 drop table [dbo].[CustomerCustomerDemo]
\r
240 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CustomerDemographics]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
\r
241 drop table [dbo].[CustomerDemographics]
\r
244 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Customers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
\r
245 drop table [dbo].[Customers]
\r
248 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[EmployeeTerritories]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
\r
249 drop table [dbo].[EmployeeTerritories]
\r
252 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Employees]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
\r
253 drop table [dbo].[Employees]
\r
256 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GH_EMPTYTABLE]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
\r
257 drop table [dbo].[GH_EMPTYTABLE]
\r
260 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Order Details]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
\r
261 drop table [dbo].[Order Details]
\r
264 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Orders]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
\r
265 drop table [dbo].[Orders]
\r
268 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Products]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
\r
269 drop table [dbo].[Products]
\r
272 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Region]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
\r
273 drop table [dbo].[Region]
\r
276 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Shippers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
\r
277 drop table [dbo].[Shippers]
\r
280 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Suppliers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
\r
281 drop table [dbo].[Suppliers]
\r
284 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Territories]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
\r
285 drop table [dbo].[Territories]
\r
288 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Types_Simple]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
\r
289 drop table [dbo].[Types_Simple]
\r
292 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Types_Extended]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
\r
293 drop table [dbo].[Types_Extended]
\r
296 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Types_Specific]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
\r
297 drop table [dbo].[Types_Specific]
\r
300 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GHSP_TYPES_SIMPLE_1]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
\r
301 drop procedure [dbo].[GHSP_TYPES_SIMPLE_1]
\r
304 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GHSP_TYPES_SIMPLE_2]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
\r
305 drop procedure [dbo].[GHSP_TYPES_SIMPLE_2]
\r
308 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GHSP_TYPES_SIMPLE_3]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
\r
309 drop procedure [dbo].[GHSP_TYPES_SIMPLE_3]
\r
312 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GHSP_TYPES_SIMPLE_4]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
\r
313 drop procedure [dbo].[GHSP_TYPES_SIMPLE_4]
\r
316 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GHSP_TYPES_SIMPLE_5]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
\r
317 drop procedure [dbo].[GHSP_TYPES_SIMPLE_5]
\r
320 SET QUOTED_IDENTIFIER OFF
\r
322 SET ANSI_NULLS OFF
\r
326 CREATE TABLE [dbo].[TYPES_SIMPLE] (
\r
328 [ID] char(10) NULL,
\r
329 [T_BIT] [bit] NULL ,
\r
331 [T_TINYINT] [tinyint] NULL ,
\r
332 [T_SMALLINT] [smallint] NULL ,
\r
333 [T_INT] [int] NULL ,
\r
334 [T_BIGINT] [bigint] NULL ,
\r
336 [T_DECIMAL] [decimal](18, 0) NULL ,
\r
337 [T_NUMERIC] [numeric](18, 0) NULL ,
\r
338 [T_FLOAT] [float] NULL ,
\r
339 [T_REAL] [real] NULL ,
\r
341 [T_CHAR] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
\r
342 [T_NCHAR] [nchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
\r
343 [T_VARCHAR] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
\r
344 [T_NVARCHAR] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
\r
348 CREATE TABLE [dbo].[TYPES_EXTENDED] (
\r
350 [ID] char(10) NULL,
\r
352 [T_TEXT] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
\r
353 [T_NTEXT] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
\r
355 [T_BINARY] [binary] (50) NULL ,
\r
356 [T_VARBINARY] [varbinary] (50) NULL ,
\r
358 [T_DATETIME] [datetime] NULL ,
\r
359 [T_SMALLDATETIME] [smalldatetime] NULL
\r
360 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
\r
362 CREATE TABLE [dbo].[TYPES_SPECIFIC] (
\r
364 [ID] char(10) NULL,
\r
365 [T_SMALLMONEY] [smallmoney] NULL ,
\r
366 [T_MONEY] [money] NULL ,
\r
367 [T_IMAGE] [image] NULL ,
\r
368 [T_UNIQUEIDENTIFIER] [uniqueidentifier] NULL ,
\r
369 [T_SQL_VARIANT] [sql_variant] NULL ,
\r
370 [T_TIMESTAMP] [timestamp] NULL
\r
371 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
\r
374 CREATE TABLE [dbo].[Categories] (
\r
375 [CategoryID] [int] IDENTITY (1, 1) NOT NULL ,
\r
376 [CategoryName] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
\r
377 [Description] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
\r
378 [Picture] [image] NULL
\r
379 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
\r
382 CREATE TABLE [dbo].[CustomerCustomerDemo] (
\r
383 [CustomerID] [nchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
\r
384 [CustomerTypeID] [nchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
\r
388 CREATE TABLE [dbo].[CustomerDemographics] (
\r
389 [CustomerTypeID] [nchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
\r
390 [CustomerDesc] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
\r
391 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
\r
394 CREATE TABLE [dbo].[Customers] (
\r
395 [CustomerID] [nchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
\r
396 [CompanyName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
\r
397 [ContactName] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
\r
398 [ContactTitle] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
\r
399 [Address] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
\r
400 [City] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
\r
401 [Region] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
\r
402 [PostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
\r
403 [Country] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
\r
404 [Phone] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
\r
405 [Fax] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
\r
409 CREATE TABLE [dbo].[EmployeeTerritories] (
\r
410 [EmployeeID] [int] NOT NULL ,
\r
411 [TerritoryID] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
\r
415 CREATE TABLE [dbo].[Employees] (
\r
416 [EmployeeID] [int] NOT NULL ,
\r
417 [LastName] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
\r
418 [FirstName] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
\r
419 [Title] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
\r
420 [TitleOfCourtesy] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
\r
421 [BirthDate] [datetime] NULL ,
\r
422 [HireDate] [datetime] NULL ,
\r
423 [Address] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
\r
424 [City] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
\r
425 [Region] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
\r
426 [PostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
\r
427 [Country] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
\r
428 [HomePhone] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
\r
429 [Extension] [nvarchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
\r
430 [Photo] [image] NULL ,
\r
431 [Notes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
\r
432 [ReportsTo] [int] NULL ,
\r
433 [PhotoPath] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
\r
434 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
\r
437 CREATE TABLE [dbo].[GH_EMPTYTABLE] (
\r
438 [Col1] [int] NULL ,
\r
439 [Col2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
\r
443 CREATE TABLE [dbo].[Order Details] (
\r
444 [OrderID] [int] NOT NULL ,
\r
445 [ProductID] [int] NOT NULL ,
\r
446 [UnitPrice] [money] NOT NULL ,
\r
447 [Quantity] [smallint] NOT NULL ,
\r
448 [Discount] [real] NOT NULL
\r
452 CREATE TABLE [dbo].[Orders] (
\r
453 [OrderID] [int] IDENTITY (1, 1) NOT NULL ,
\r
454 [CustomerID] [nchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
\r
455 [EmployeeID] [int] NULL ,
\r
456 [OrderDate] [datetime] NULL ,
\r
457 [RequiredDate] [datetime] NULL ,
\r
458 [ShippedDate] [datetime] NULL ,
\r
459 [ShipVia] [int] NULL ,
\r
460 [Freight] [money] NULL ,
\r
461 [ShipName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
\r
462 [ShipAddress] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
\r
463 [ShipCity] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
\r
464 [ShipRegion] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
\r
465 [ShipPostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
\r
466 [ShipCountry] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
\r
470 CREATE TABLE [dbo].[Products] (
\r
471 [ProductID] [int] IDENTITY (1, 1) NOT NULL ,
\r
472 [ProductName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
\r
473 [SupplierID] [int] NULL ,
\r
474 [CategoryID] [int] NULL ,
\r
475 [QuantityPerUnit] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
\r
476 [UnitPrice] [money] NULL ,
\r
477 [UnitsInStock] [smallint] NULL ,
\r
478 [UnitsOnOrder] [smallint] NULL ,
\r
479 [ReorderLevel] [smallint] NULL ,
\r
480 [Discontinued] [bit] NOT NULL
\r
484 CREATE TABLE [dbo].[Region] (
\r
485 [RegionID] [int] NOT NULL ,
\r
486 [RegionDescription] [nchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
\r
490 CREATE TABLE [dbo].[Shippers] (
\r
491 [ShipperID] [int] IDENTITY (1, 1) NOT NULL ,
\r
492 [CompanyName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
\r
493 [Phone] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
\r
497 CREATE TABLE [dbo].[Suppliers] (
\r
498 [SupplierID] [int] IDENTITY (1, 1) NOT NULL ,
\r
499 [CompanyName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
\r
500 [ContactName] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
\r
501 [ContactTitle] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
\r
502 [Address] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
\r
503 [City] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
\r
504 [Region] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
\r
505 [PostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
\r
506 [Country] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
\r
507 [Phone] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
\r
508 [Fax] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
\r
509 [HomePage] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
\r
510 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
\r
513 CREATE TABLE [dbo].[Territories] (
\r
514 [TerritoryID] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
\r
515 [TerritoryDescription] [nchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
\r
516 [RegionID] [int] NOT NULL
\r
520 ALTER TABLE [dbo].[Categories] WITH NOCHECK ADD
\r
521 CONSTRAINT [PK_Categories] PRIMARY KEY CLUSTERED
\r
527 ALTER TABLE [dbo].[Customers] WITH NOCHECK ADD
\r
528 CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED
\r
534 ALTER TABLE [dbo].[Employees] WITH NOCHECK ADD
\r
535 CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED
\r
541 ALTER TABLE [dbo].[Order Details] WITH NOCHECK ADD
\r
542 CONSTRAINT [PK_Order_Details] PRIMARY KEY CLUSTERED
\r
549 ALTER TABLE [dbo].[Orders] WITH NOCHECK ADD
\r
550 CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED
\r
556 ALTER TABLE [dbo].[Products] WITH NOCHECK ADD
\r
557 CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED
\r
563 ALTER TABLE [dbo].[Shippers] WITH NOCHECK ADD
\r
564 CONSTRAINT [PK_Shippers] PRIMARY KEY CLUSTERED
\r
570 ALTER TABLE [dbo].[Suppliers] WITH NOCHECK ADD
\r
571 CONSTRAINT [PK_Suppliers] PRIMARY KEY CLUSTERED
\r
577 CREATE INDEX [CategoryName] ON [dbo].[Categories]([CategoryName]) ON [PRIMARY]
\r
580 ALTER TABLE [dbo].[CustomerCustomerDemo] ADD
\r
581 CONSTRAINT [PK_CustomerCustomerDemo] PRIMARY KEY NONCLUSTERED
\r
588 ALTER TABLE [dbo].[CustomerDemographics] ADD
\r
589 CONSTRAINT [PK_CustomerDemographics] PRIMARY KEY NONCLUSTERED
\r
595 CREATE INDEX [City] ON [dbo].[Customers]([City]) ON [PRIMARY]
\r
598 CREATE INDEX [CompanyName] ON [dbo].[Customers]([CompanyName]) ON [PRIMARY]
\r
601 CREATE INDEX [PostalCode] ON [dbo].[Customers]([PostalCode]) ON [PRIMARY]
\r
604 CREATE INDEX [Region] ON [dbo].[Customers]([Region]) ON [PRIMARY]
\r
607 ALTER TABLE [dbo].[EmployeeTerritories] ADD
\r
608 CONSTRAINT [PK_EmployeeTerritories] PRIMARY KEY NONCLUSTERED
\r
615 ALTER TABLE [dbo].[Employees] ADD
\r
616 CONSTRAINT [CK_Birthdate] CHECK ([BirthDate] < getdate())
\r
619 CREATE INDEX [LastName] ON [dbo].[Employees]([LastName]) ON [PRIMARY]
\r
622 CREATE INDEX [PostalCode] ON [dbo].[Employees]([PostalCode]) ON [PRIMARY]
\r
625 ALTER TABLE [dbo].[Order Details] ADD
\r
626 CONSTRAINT [DF_Order_Details_UnitPrice] DEFAULT (0) FOR [UnitPrice],
\r
627 CONSTRAINT [DF_Order_Details_Quantity] DEFAULT (1) FOR [Quantity],
\r
628 CONSTRAINT [DF_Order_Details_Discount] DEFAULT (0) FOR [Discount],
\r
629 CONSTRAINT [CK_Discount] CHECK ([Discount] >= 0 and [Discount] <= 1),
\r
630 CONSTRAINT [CK_Quantity] CHECK ([Quantity] > 0),
\r
631 CONSTRAINT [CK_UnitPrice] CHECK ([UnitPrice] >= 0)
\r
634 CREATE INDEX [OrderID] ON [dbo].[Order Details]([OrderID]) ON [PRIMARY]
\r
637 CREATE INDEX [OrdersOrder_Details] ON [dbo].[Order Details]([OrderID]) ON [PRIMARY]
\r
640 CREATE INDEX [ProductID] ON [dbo].[Order Details]([ProductID]) ON [PRIMARY]
\r
643 CREATE INDEX [ProductsOrder_Details] ON [dbo].[Order Details]([ProductID]) ON [PRIMARY]
\r
646 ALTER TABLE [dbo].[Orders] ADD
\r
647 CONSTRAINT [DF_Orders_Freight] DEFAULT (0) FOR [Freight]
\r
650 CREATE INDEX [CustomerID] ON [dbo].[Orders]([CustomerID]) ON [PRIMARY]
\r
653 CREATE INDEX [CustomersOrders] ON [dbo].[Orders]([CustomerID]) ON [PRIMARY]
\r
656 CREATE INDEX [EmployeeID] ON [dbo].[Orders]([EmployeeID]) ON [PRIMARY]
\r
659 CREATE INDEX [EmployeesOrders] ON [dbo].[Orders]([EmployeeID]) ON [PRIMARY]
\r
662 CREATE INDEX [OrderDate] ON [dbo].[Orders]([OrderDate]) ON [PRIMARY]
\r
665 CREATE INDEX [ShippedDate] ON [dbo].[Orders]([ShippedDate]) ON [PRIMARY]
\r
668 CREATE INDEX [ShippersOrders] ON [dbo].[Orders]([ShipVia]) ON [PRIMARY]
\r
671 CREATE INDEX [ShipPostalCode] ON [dbo].[Orders]([ShipPostalCode]) ON [PRIMARY]
\r
674 ALTER TABLE [dbo].[Products] ADD
\r
675 CONSTRAINT [DF_Products_UnitPrice] DEFAULT (0) FOR [UnitPrice],
\r
676 CONSTRAINT [DF_Products_UnitsInStock] DEFAULT (0) FOR [UnitsInStock],
\r
677 CONSTRAINT [DF_Products_UnitsOnOrder] DEFAULT (0) FOR [UnitsOnOrder],
\r
678 CONSTRAINT [DF_Products_ReorderLevel] DEFAULT (0) FOR [ReorderLevel],
\r
679 CONSTRAINT [DF_Products_Discontinued] DEFAULT (0) FOR [Discontinued],
\r
680 CONSTRAINT [CK_Products_UnitPrice] CHECK ([UnitPrice] >= 0),
\r
681 CONSTRAINT [CK_ReorderLevel] CHECK ([ReorderLevel] >= 0),
\r
682 CONSTRAINT [CK_UnitsInStock] CHECK ([UnitsInStock] >= 0),
\r
683 CONSTRAINT [CK_UnitsOnOrder] CHECK ([UnitsOnOrder] >= 0)
\r
686 CREATE INDEX [CategoriesProducts] ON [dbo].[Products]([CategoryID]) ON [PRIMARY]
\r
689 CREATE INDEX [CategoryID] ON [dbo].[Products]([CategoryID]) ON [PRIMARY]
\r
692 CREATE INDEX [ProductName] ON [dbo].[Products]([ProductName]) ON [PRIMARY]
\r
695 CREATE INDEX [SupplierID] ON [dbo].[Products]([SupplierID]) ON [PRIMARY]
\r
698 CREATE INDEX [SuppliersProducts] ON [dbo].[Products]([SupplierID]) ON [PRIMARY]
\r
701 ALTER TABLE [dbo].[Region] ADD
\r
702 CONSTRAINT [PK_Region] PRIMARY KEY NONCLUSTERED
\r
708 CREATE INDEX [CompanyName] ON [dbo].[Suppliers]([CompanyName]) ON [PRIMARY]
\r
711 CREATE INDEX [PostalCode] ON [dbo].[Suppliers]([PostalCode]) ON [PRIMARY]
\r
714 ALTER TABLE [dbo].[Territories] ADD
\r
715 CONSTRAINT [PK_Territories] PRIMARY KEY NONCLUSTERED
\r
721 ALTER TABLE [dbo].[CustomerCustomerDemo] ADD
\r
722 CONSTRAINT [FK_CustomerCustomerDemo] FOREIGN KEY
\r
725 ) REFERENCES [dbo].[CustomerDemographics] (
\r
728 CONSTRAINT [FK_CustomerCustomerDemo_Customers] FOREIGN KEY
\r
731 ) REFERENCES [dbo].[Customers] (
\r
736 ALTER TABLE [dbo].[EmployeeTerritories] ADD
\r
737 CONSTRAINT [FK_EmployeeTerritories_Employees] FOREIGN KEY
\r
740 ) REFERENCES [dbo].[Employees] (
\r
743 CONSTRAINT [FK_EmployeeTerritories_Territories] FOREIGN KEY
\r
746 ) REFERENCES [dbo].[Territories] (
\r
751 ALTER TABLE [dbo].[Employees] ADD
\r
752 CONSTRAINT [FK_Employees_Employees] FOREIGN KEY
\r
755 ) REFERENCES [dbo].[Employees] (
\r
760 ALTER TABLE [dbo].[Order Details] ADD
\r
761 CONSTRAINT [FK_Order_Details_Orders] FOREIGN KEY
\r
764 ) REFERENCES [dbo].[Orders] (
\r
767 CONSTRAINT [FK_Order_Details_Products] FOREIGN KEY
\r
770 ) REFERENCES [dbo].[Products] (
\r
775 ALTER TABLE [dbo].[Orders] ADD
\r
776 CONSTRAINT [FK_Orders_Customers] FOREIGN KEY
\r
779 ) REFERENCES [dbo].[Customers] (
\r
782 CONSTRAINT [FK_Orders_Employees] FOREIGN KEY
\r
785 ) REFERENCES [dbo].[Employees] (
\r
788 CONSTRAINT [FK_Orders_Shippers] FOREIGN KEY
\r
791 ) REFERENCES [dbo].[Shippers] (
\r
796 ALTER TABLE [dbo].[Products] ADD
\r
797 CONSTRAINT [FK_Products_Categories] FOREIGN KEY
\r
800 ) REFERENCES [dbo].[Categories] (
\r
803 CONSTRAINT [FK_Products_Suppliers] FOREIGN KEY
\r
806 ) REFERENCES [dbo].[Suppliers] (
\r
811 ALTER TABLE [dbo].[Territories] ADD
\r
812 CONSTRAINT [FK_Territories_Region] FOREIGN KEY
\r
815 ) REFERENCES [dbo].[Region] (
\r
820 SET QUOTED_IDENTIFIER ON
\r
825 create view "Current Product List" AS
\r
826 SELECT Product_List.ProductID, Product_List.ProductName
\r
827 FROM Products AS Product_List
\r
828 WHERE (((Product_List.Discontinued)=0))
\r
829 --ORDER BY Product_List.ProductName
\r
832 SET QUOTED_IDENTIFIER OFF
\r
837 SET QUOTED_IDENTIFIER ON
\r
842 create view "Orders Qry" AS
\r
843 SELECT Orders.OrderID, Orders.CustomerID, Orders.EmployeeID, Orders.OrderDate, Orders.RequiredDate,
\r
844 Orders.ShippedDate, Orders.ShipVia, Orders.Freight, Orders.ShipName, Orders.ShipAddress, Orders.ShipCity,
\r
845 Orders.ShipRegion, Orders.ShipPostalCode, Orders.ShipCountry,
\r
846 Customers.CompanyName, Customers.Address, Customers.City, Customers.Region, Customers.PostalCode, Customers.Country
\r
847 FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
\r
850 SET QUOTED_IDENTIFIER OFF
\r
855 SET QUOTED_IDENTIFIER ON
\r
860 create view "Products Above Average Price" AS
\r
861 SELECT Products.ProductName, Products.UnitPrice
\r
863 WHERE Products.UnitPrice>(SELECT AVG(UnitPrice) From Products)
\r
864 --ORDER BY Products.UnitPrice DESC
\r
867 SET QUOTED_IDENTIFIER OFF
\r
872 SET QUOTED_IDENTIFIER ON
\r
877 create view "Products by Category" AS
\r
878 SELECT Categories.CategoryName, Products.ProductName, Products.QuantityPerUnit, Products.UnitsInStock, Products.Discontinued
\r
879 FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID
\r
880 WHERE Products.Discontinued <> 1
\r
881 --ORDER BY Categories.CategoryName, Products.ProductName
\r
884 SET QUOTED_IDENTIFIER OFF
\r
889 SET QUOTED_IDENTIFIER ON
\r
894 create view "Quarterly Orders" AS
\r
895 SELECT DISTINCT Customers.CustomerID, Customers.CompanyName, Customers.City, Customers.Country
\r
896 FROM Customers RIGHT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
\r
897 WHERE Orders.OrderDate BETWEEN '19970101' And '19971231'
\r
900 SET QUOTED_IDENTIFIER OFF
\r
905 SET QUOTED_IDENTIFIER ON
\r
910 create view Invoices AS
\r
911 SELECT Orders.ShipName, Orders.ShipAddress, Orders.ShipCity, Orders.ShipRegion, Orders.ShipPostalCode,
\r
912 Orders.ShipCountry, Orders.CustomerID, Customers.CompanyName AS CustomerName, Customers.Address, Customers.City,
\r
913 Customers.Region, Customers.PostalCode, Customers.Country,
\r
914 (FirstName + ' ' + LastName) AS Salesperson,
\r
915 Orders.OrderID, Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate, Shippers.CompanyName As ShipperName,
\r
916 "Order Details".ProductID, Products.ProductName, "Order Details".UnitPrice, "Order Details".Quantity,
\r
917 "Order Details".Discount,
\r
918 (CONVERT(money,("Order Details".UnitPrice*Quantity*(1-Discount)/100))*100) AS ExtendedPrice, Orders.Freight
\r
919 FROM Shippers INNER JOIN
\r
920 (Products INNER JOIN
\r
922 (Employees INNER JOIN
\r
923 (Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID)
\r
924 ON Employees.EmployeeID = Orders.EmployeeID)
\r
925 INNER JOIN "Order Details" ON Orders.OrderID = "Order Details".OrderID)
\r
926 ON Products.ProductID = "Order Details".ProductID)
\r
927 ON Shippers.ShipperID = Orders.ShipVia
\r
930 SET QUOTED_IDENTIFIER OFF
\r
935 SET QUOTED_IDENTIFIER ON
\r
940 create view "Order Details Extended" AS
\r
941 SELECT "Order Details".OrderID, "Order Details".ProductID, Products.ProductName,
\r
942 "Order Details".UnitPrice, "Order Details".Quantity, "Order Details".Discount,
\r
943 (CONVERT(money,("Order Details".UnitPrice*Quantity*(1-Discount)/100))*100) AS ExtendedPrice
\r
944 FROM Products INNER JOIN "Order Details" ON Products.ProductID = "Order Details".ProductID
\r
945 --ORDER BY "Order Details".OrderID
\r
948 SET QUOTED_IDENTIFIER OFF
\r
953 SET QUOTED_IDENTIFIER ON
\r
958 create view "Order Subtotals" AS
\r
959 SELECT "Order Details".OrderID, Sum(CONVERT(money,("Order Details".UnitPrice*Quantity*(1-Discount)/100))*100) AS Subtotal
\r
960 FROM "Order Details"
\r
961 GROUP BY "Order Details".OrderID
\r
964 SET QUOTED_IDENTIFIER OFF
\r
969 SET QUOTED_IDENTIFIER ON
\r
974 create view "Product Sales for 1997" AS
\r
975 SELECT Categories.CategoryName, Products.ProductName,
\r
976 Sum(CONVERT(money,("Order Details".UnitPrice*Quantity*(1-Discount)/100))*100) AS ProductSales
\r
977 FROM (Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID)
\r
978 INNER JOIN (Orders
\r
979 INNER JOIN "Order Details" ON Orders.OrderID = "Order Details".OrderID)
\r
980 ON Products.ProductID = "Order Details".ProductID
\r
981 WHERE (((Orders.ShippedDate) Between '19970101' And '19971231'))
\r
982 GROUP BY Categories.CategoryName, Products.ProductName
\r
985 SET QUOTED_IDENTIFIER OFF
\r
990 SET QUOTED_IDENTIFIER ON
\r
995 create view "Category Sales for 1997" AS
\r
996 SELECT "Product Sales for 1997".CategoryName, Sum("Product Sales for 1997".ProductSales) AS CategorySales
\r
997 FROM "Product Sales for 1997"
\r
998 GROUP BY "Product Sales for 1997".CategoryName
\r
1001 SET QUOTED_IDENTIFIER OFF
\r
1003 SET ANSI_NULLS ON
\r
1006 SET QUOTED_IDENTIFIER ON
\r
1008 SET ANSI_NULLS ON
\r
1011 create view "Sales Totals by Amount" AS
\r
1012 SELECT "Order Subtotals".Subtotal AS SaleAmount, Orders.OrderID, Customers.CompanyName, Orders.ShippedDate
\r
1013 FROM Customers INNER JOIN
\r
1014 (Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID)
\r
1015 ON Customers.CustomerID = Orders.CustomerID
\r
1016 WHERE ("Order Subtotals".Subtotal >2500) AND (Orders.ShippedDate BETWEEN '19970101' And '19971231')
\r
1019 SET QUOTED_IDENTIFIER OFF
\r
1021 SET ANSI_NULLS ON
\r
1024 SET QUOTED_IDENTIFIER ON
\r
1026 SET ANSI_NULLS ON
\r
1029 create view "Sales by Category" AS
\r
1030 SELECT Categories.CategoryID, Categories.CategoryName, Products.ProductName,
\r
1031 Sum("Order Details Extended".ExtendedPrice) AS ProductSales
\r
1032 FROM Categories INNER JOIN
\r
1033 (Products INNER JOIN
\r
1034 (Orders INNER JOIN "Order Details Extended" ON Orders.OrderID = "Order Details Extended".OrderID)
\r
1035 ON Products.ProductID = "Order Details Extended".ProductID)
\r
1036 ON Categories.CategoryID = Products.CategoryID
\r
1037 WHERE Orders.OrderDate BETWEEN '19970101' And '19971231'
\r
1038 GROUP BY Categories.CategoryID, Categories.CategoryName, Products.ProductName
\r
1039 --ORDER BY Products.ProductName
\r
1042 SET QUOTED_IDENTIFIER OFF
\r
1044 SET ANSI_NULLS ON
\r
1047 SET QUOTED_IDENTIFIER ON
\r
1049 SET ANSI_NULLS ON
\r
1052 create view "Summary of Sales by Quarter" AS
\r
1053 SELECT Orders.ShippedDate, Orders.OrderID, "Order Subtotals".Subtotal
\r
1054 FROM Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID
\r
1055 WHERE Orders.ShippedDate IS NOT NULL
\r
1056 --ORDER BY Orders.ShippedDate
\r
1059 SET QUOTED_IDENTIFIER OFF
\r
1061 SET ANSI_NULLS ON
\r
1064 SET QUOTED_IDENTIFIER ON
\r
1066 SET ANSI_NULLS ON
\r
1069 create view "Summary of Sales by Year" AS
\r
1070 SELECT Orders.ShippedDate, Orders.OrderID, "Order Subtotals".Subtotal
\r
1071 FROM Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID
\r
1072 WHERE Orders.ShippedDate IS NOT NULL
\r
1073 --ORDER BY Orders.ShippedDate
\r
1076 SET QUOTED_IDENTIFIER OFF
\r
1078 SET ANSI_NULLS ON
\r
1081 SET QUOTED_IDENTIFIER ON
\r
1083 SET ANSI_NULLS ON
\r
1086 CREATE PROCEDURE CustOrderHist @CustomerID nchar(5)
\r
1088 SELECT ProductName, Total=SUM(Quantity)
\r
1089 FROM Products P, [Order Details] OD, Orders O, Customers C
\r
1090 WHERE C.CustomerID = @CustomerID
\r
1091 AND C.CustomerID = O.CustomerID AND O.OrderID = OD.OrderID AND OD.ProductID = P.ProductID
\r
1092 GROUP BY ProductName
\r
1095 SET QUOTED_IDENTIFIER OFF
\r
1097 SET ANSI_NULLS ON
\r
1100 SET QUOTED_IDENTIFIER ON
\r
1102 SET ANSI_NULLS ON
\r
1105 CREATE PROCEDURE CustOrdersDetail @OrderID int
\r
1107 SELECT ProductName,
\r
1108 UnitPrice=ROUND(Od.UnitPrice, 2),
\r
1110 Discount=CONVERT(int, Discount * 100),
\r
1111 ExtendedPrice=ROUND(CONVERT(money, Quantity * (1 - Discount) * Od.UnitPrice), 2)
\r
1112 FROM Products P, [Order Details] Od
\r
1113 WHERE Od.ProductID = P.ProductID and Od.OrderID = @OrderID
\r
1116 SET QUOTED_IDENTIFIER OFF
\r
1118 SET ANSI_NULLS ON
\r
1121 SET QUOTED_IDENTIFIER ON
\r
1123 SET ANSI_NULLS ON
\r
1126 CREATE PROCEDURE CustOrdersOrders @CustomerID nchar(5)
\r
1133 WHERE CustomerID = @CustomerID
\r
1137 SET QUOTED_IDENTIFIER OFF
\r
1139 SET ANSI_NULLS ON
\r
1142 SET QUOTED_IDENTIFIER ON
\r
1144 SET ANSI_NULLS ON
\r
1147 create procedure "Employee Sales by Country"
\r
1148 @Beginning_Date DateTime, @Ending_Date DateTime AS
\r
1149 SELECT Employees.Country, Employees.LastName, Employees.FirstName, Orders.ShippedDate, Orders.OrderID, "Order Subtotals".Subtotal AS SaleAmount
\r
1150 FROM Employees INNER JOIN
\r
1151 (Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID)
\r
1152 ON Employees.EmployeeID = Orders.EmployeeID
\r
1153 WHERE Orders.ShippedDate Between @Beginning_Date And @Ending_Date
\r
1156 SET QUOTED_IDENTIFIER OFF
\r
1158 SET ANSI_NULLS ON
\r
1161 SET QUOTED_IDENTIFIER ON
\r
1163 SET ANSI_NULLS ON
\r
1166 CREATE PROCEDURE GH_CREATETABLE
\r
1169 --craete a temporary table
\r
1170 Create Table #temp_tbl (
\r
1174 --insert values to the table
\r
1175 insert into #temp_tbl values (11,12)
\r
1176 insert into #temp_tbl values (21,22)
\r
1177 insert into #temp_tbl values (31,32)
\r
1178 --execute select on the created table
\r
1179 select col1 as Value1, col2 as Value2 from #temp_tbl;
\r
1180 --Update Return code
\r
1183 SET QUOTED_IDENTIFIER OFF
\r
1185 SET ANSI_NULLS ON
\r
1188 SET QUOTED_IDENTIFIER ON
\r
1190 SET ANSI_NULLS ON
\r
1193 CREATE PROCEDURE GH_MultiRecordSets
\r
1196 SELECT EmployeeId, LastName FROM Employees where EmployeeId in (1,2) order by EmployeeId asc;
\r
1197 SELECT CustomerId, CompanyName,ContactName FROM Customers where CustomerId in ('MORGK','NORTS') order by customerid asc;
\r
1198 -- return empty result set
\r
1199 SELECT OrderId, ShipAddress,ShipVia, ShipCity FROM Orders where OrderId=-1 ;
\r
1203 CREATE procedure GH_INOUT1
\r
1204 @INPARAM varchar(20) ,
\r
1205 @OUTPARAM int output
\r
1207 declare @L_INPARAM varchar(30)
\r
1208 select L_INPARAM = @INPARAM
\r
1209 select @OUTPARAM = 100
\r
1213 CREATE procedure GH_REFCURSOR1
\r
1215 SELECT EmployeeId, LastName FROM Employees
\r
1216 WHERE EmployeeId=1;
\r
1219 CREATE procedure GH_REFCURSOR2
\r
1220 @IN_EMPLOYEEID int
\r
1222 SELECT EmployeeId, LastName FROM Employees
\r
1223 where EmployeeId = @IN_EMPLOYEEID
\r
1227 CREATE procedure GH_REFCURSOR3
\r
1228 @IN_LASTNAME varchar(20) AS
\r
1229 SELECT EmployeeId, LastName FROM Employees
\r
1230 where LastName = @IN_LASTNAME
\r
1235 SET QUOTED_IDENTIFIER OFF
\r
1237 SET ANSI_NULLS ON
\r
1240 SET QUOTED_IDENTIFIER ON
\r
1242 SET ANSI_NULLS ON
\r
1245 create procedure "Sales by Year"
\r
1246 @Beginning_Date DateTime, @Ending_Date DateTime AS
\r
1247 SELECT Orders.ShippedDate, Orders.OrderID, "Order Subtotals".Subtotal, DATENAME(yy,ShippedDate) AS Year
\r
1248 FROM Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID
\r
1249 WHERE Orders.ShippedDate Between @Beginning_Date And @Ending_Date
\r
1252 SET QUOTED_IDENTIFIER OFF
\r
1254 SET ANSI_NULLS ON
\r
1257 SET QUOTED_IDENTIFIER ON
\r
1259 SET ANSI_NULLS ON
\r
1262 CREATE PROCEDURE SalesByCategory
\r
1263 @CategoryName nvarchar(15), @OrdYear nvarchar(4) = '1998'
\r
1265 IF @OrdYear != '1996' AND @OrdYear != '1997' AND @OrdYear != '1998'
\r
1267 SELECT @OrdYear = '1998'
\r
1269 SELECT ProductName,
\r
1270 TotalPurchase=ROUND(SUM(CONVERT(decimal(14,2), OD.Quantity * (1-OD.Discount) * OD.UnitPrice)), 0)
\r
1271 FROM [Order Details] OD, Orders O, Products P, Categories C
\r
1272 WHERE OD.OrderID = O.OrderID
\r
1273 AND OD.ProductID = P.ProductID
\r
1274 AND P.CategoryID = C.CategoryID
\r
1275 AND C.CategoryName = @CategoryName
\r
1276 AND SUBSTRING(CONVERT(nvarchar(22), O.OrderDate, 111), 1, 4) = @OrdYear
\r
1277 GROUP BY ProductName
\r
1278 ORDER BY ProductName
\r
1281 SET QUOTED_IDENTIFIER OFF
\r
1283 SET ANSI_NULLS ON
\r
1286 SET QUOTED_IDENTIFIER ON
\r
1288 SET ANSI_NULLS ON
\r
1291 create procedure "Ten Most Expensive Products" AS
\r
1293 SELECT Products.ProductName AS TenMostExpensiveProducts, Products.UnitPrice
\r
1295 ORDER BY Products.UnitPrice DESC
\r
1298 SET QUOTED_IDENTIFIER OFF
\r
1300 SET ANSI_NULLS ON
\r
1303 CREATE PROCEDURE GHSP_TYPES_SIMPLE_1
\r
1305 @T_TINYINT tinyint,
\r
1306 @T_SMALLINT smallint ,
\r
1309 @T_DECIMAL decimal(18, 0),
\r
1310 @T_NUMERIC numeric(18, 0) ,
\r
1313 @T_CHAR char (10),
\r
1314 @T_NCHAR nchar (10),
\r
1315 @T_VARCHAR varchar (50) ,
\r
1316 @T_NVARCHAR nvarchar (50)
\r
1318 SELECT @T_BIT as 'T_BIT', @T_TINYINT as 'T_TINYINT', @T_SMALLINT as 'T_SMALLINT' , @T_INT as 'T_INT', @T_BIGINT as 'T_BIGINT', @T_DECIMAL as 'T_DECIMAL',
\r
1319 @T_NUMERIC as 'T_NUMERIC' , @T_FLOAT as 'T_FLOAT' , @T_REAL as 'T_REAL' , @T_CHAR as 'T_CHAR', @T_NCHAR as 'T_NCHAR', @T_VARCHAR as 'T_VARCHAR' , @T_NVARCHAR as 'T_NVARCHAR'
\r
1321 SET QUOTED_IDENTIFIER OFF
\r
1323 SET ANSI_NULLS ON
\r
1326 SET QUOTED_IDENTIFIER OFF
\r
1328 SET ANSI_NULLS OFF
\r
1331 CREATE PROCEDURE GHSP_TYPES_SIMPLE_2
\r
1332 @T_BIT bit output,
\r
1333 @T_TINYINT tinyint output,
\r
1334 @T_SMALLINT smallint output,
\r
1335 @T_INT int output,
\r
1336 @T_BIGINT bigint output,
\r
1337 @T_DECIMAL decimal(18, 0) output,
\r
1338 @T_NUMERIC numeric(18, 0) output,
\r
1339 @T_FLOAT float output,
\r
1340 @T_REAL real output,
\r
1341 @T_CHAR char (10) output,
\r
1342 @T_NCHAR nchar (10) output,
\r
1343 @T_VARCHAR varchar (50) output,
\r
1344 @T_NVARCHAR nvarchar (50) output
\r
1348 ELSE IF (@T_BIT=1)
\r
1350 SELECT @T_TINYINT = @T_TINYINT*2
\r
1351 SELECT @T_SMALLINT = @T_SMALLINT*2
\r
1352 SELECT @T_INT = @T_INT*2
\r
1353 SELECT @T_BIGINT = @T_BIGINT*2
\r
1354 SELECT @T_DECIMAL = @T_DECIMAL*2
\r
1355 SELECT @T_NUMERIC = @T_NUMERIC*2
\r
1356 SELECT @T_FLOAT = @T_FLOAT*2
\r
1357 SELECT @T_REAL = @T_REAL*2
\r
1358 SELECT @T_CHAR = UPPER(@T_CHAR)
\r
1359 SELECT @T_NCHAR =UPPER(@T_NCHAR)
\r
1360 SELECT @T_VARCHAR = UPPER(@T_VARCHAR)
\r
1361 SELECT @T_NVARCHAR = UPPER(@T_NVARCHAR)
\r
1363 SET QUOTED_IDENTIFIER OFF
\r
1365 SET ANSI_NULLS ON
\r
1368 SET QUOTED_IDENTIFIER OFF
\r
1370 SET ANSI_NULLS OFF
\r
1373 CREATE PROCEDURE GHSP_TYPES_SIMPLE_3
\r
1375 @T_BIT bit output,
\r
1376 @T_TINYINT tinyint output,
\r
1377 @T_SMALLINT smallint output,
\r
1378 @T_INT int output,
\r
1379 @T_BIGINT bigint output,
\r
1380 @T_DECIMAL decimal(18, 0) output,
\r
1381 @T_NUMERIC numeric(18, 0) output,
\r
1382 @T_FLOAT float output,
\r
1383 @T_REAL real output,
\r
1384 @T_CHAR char (10) output,
\r
1385 @T_NCHAR nchar (10) output,
\r
1386 @T_VARCHAR varchar (50) output,
\r
1387 @T_NVARCHAR nvarchar (50) output
\r
1389 SELECT @T_BIT = T_BIT, @T_TINYINT = T_TINYINT, @T_SMALLINT = T_SMALLINT , @T_INT = T_INT, @T_BIGINT = T_BIGINT, @T_DECIMAL = T_DECIMAL ,
\r
1390 @T_NUMERIC = T_NUMERIC , @T_FLOAT = T_FLOAT , @T_REAL = T_REAL , @T_CHAR = T_CHAR, @T_NCHAR = T_NCHAR,
\r
1391 @T_VARCHAR = T_VARCHAR, @T_NVARCHAR = T_NVARCHAR FROM TYPES_SIMPLE WHERE ID = @ID
\r
1393 SET QUOTED_IDENTIFIER OFF
\r
1395 SET ANSI_NULLS ON
\r
1398 SET QUOTED_IDENTIFIER OFF
\r
1400 SET ANSI_NULLS OFF
\r
1403 CREATE PROCEDURE GHSP_TYPES_SIMPLE_4
\r
1407 insert into TYPES_SIMPLE(ID,T_INT) values (@ID,50)
\r
1408 SELECT * FROM TYPES_SIMPLE WHERE ID = @ID
\r
1410 update TYPES_SIMPLE set T_INT=60 where Id = @ID
\r
1411 SELECT * FROM TYPES_SIMPLE WHERE ID = @ID
\r
1413 delete from TYPES_SIMPLE WHERE ID = @ID
\r
1414 SELECT * FROM TYPES_SIMPLE WHERE ID = @ID
\r
1416 SET QUOTED_IDENTIFIER OFF
\r
1418 SET ANSI_NULLS ON
\r
1421 SET QUOTED_IDENTIFIER OFF
\r
1423 SET ANSI_NULLS OFF
\r
1426 CREATE PROCEDURE GHSP_TYPES_SIMPLE_5
\r
1428 DECLARE @T_BIT bit
\r
1429 DECLARE @T_TINYINT tinyint
\r
1430 DECLARE @T_SMALLINT smallint
\r
1431 DECLARE @T_INT int
\r
1432 DECLARE @T_BIGINT bigint
\r
1433 DECLARE @T_DECIMAL decimal(18,0)
\r
1434 DECLARE @T_NUMERIC numeric(18,0)
\r
1435 DECLARE @T_FLOAT float
\r
1436 DECLARE @T_REAL real
\r
1437 DECLARE @T_CHAR char(10)
\r
1438 DECLARE @T_NCHAR nchar(10)
\r
1439 DECLARE @T_VARCHAR varchar(50)
\r
1440 DECLARE @T_NVARCHAR nvarchar(50)
\r
1443 SELECT @T_TINYINT = 25
\r
1444 SELECT @T_SMALLINT = 77
\r
1445 SELECT @T_INT = 2525
\r
1446 SELECT @T_BIGINT = 25251414
\r
1447 SELECT @T_DECIMAL = 10
\r
1448 SELECT @T_NUMERIC = 123123
\r
1449 SELECT @T_FLOAT = 17.1414257
\r
1450 SELECT @T_REAL = 0.71425
\r
1451 SELECT @T_CHAR = 'abcdefghij'
\r
1452 SELECT @T_NCHAR = N'klmnopqrst'
\r
1453 SELECT @T_VARCHAR = 'qwertasdfg'
\r
1454 SELECT @T_NVARCHAR = N'qwertasdfg'
\r
1456 SELECT @T_BIT as 'T_BIT', @T_TINYINT as 'T_TINYINT', @T_SMALLINT as 'T_SMALLINT' , @T_INT as 'T_INT', @T_BIGINT as 'T_BIGINT', @T_DECIMAL as 'T_DECIMAL', @T_NUMERIC as 'T_NUMERIC' , @T_FLOAT as 'T_FLOAT' , @T_REAL as 'T_REAL' , @T_CHAR as 'T_CHAR', @T_NCHAR as 'T_NCHAR', @T_VARCHAR as 'T_VARCHAR' , @T_NVARCHAR as 'T_NVARCHAR'
\r
1459 SET QUOTED_IDENTIFIER OFF
\r
1461 SET ANSI_NULLS ON
\r
1465 if not exists (select * from master.dbo.syslogins where loginname = N'mainsoft')
\r
1467 declare @logindb nvarchar(132), @loginlang nvarchar(132) select @logindb = N'GHTDB', @loginlang = N'us_english'
\r
1468 if @logindb is null or not exists (select * from master.dbo.sysdatabases where name = @logindb)
\r
1469 select @logindb = N'master'
\r
1470 if @loginlang is null or (not exists (select * from master.dbo.syslanguages where name = @loginlang) and @loginlang <> N'us_english')
\r
1471 select @loginlang = @@language
\r
1472 exec sp_addlogin N'mainsoft', N'Sql123', @logindb, @loginlang
\r
1476 exec sp_addsrvrolemember N'mainsoft', sysadmin
\r
1480 if not exists (select * from dbo.sysusers where name = N'mainsoft' and uid < 16382)
\r
1481 EXEC sp_grantdbaccess N'mainsoft', N'mainsoft'
\r
1484 SET QUOTED_IDENTIFIER ON
\r
1486 SET ANSI_NULLS ON
\r
1489 if exists (select * from dbo.sysobjects where id = object_id(N'[mainsoft].[CategoriesNew]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
\r
1490 drop table [mainsoft].[CategoriesNew]
\r
1493 CREATE TABLE [mainsoft].[CategoriesNew] (
\r
1494 [CategoryID] [int] IDENTITY (1, 1) NOT NULL ,
\r
1495 [CategoryName] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
\r
1496 [Description] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
\r
1497 [Picture] [image] NULL
\r
1498 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
\r
1501 CREATE TABLE [mainsoft].[Categories] (
\r
1502 [CategoryID] [nvarchar] (15) NOT NULL ,
\r
1503 [CategoryName] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
\r
1504 [Description] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
\r
1505 [Picture] [int] NULL
\r
1506 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
\r
1509 CREATE procedure [mainsoft].[GH_DUMMY]
\r
1510 @EmployeeIdPrm char (10)
\r
1512 SELECT * FROM Employees where EmployeeID > @EmployeeIdPrm
\r
1514 SET QUOTED_IDENTIFIER OFF
\r
1516 SET ANSI_NULLS ON
\r
1519 ------------------------------------------------------------------------------------------------------------------
\r
1520 ------------------------------------------------------------------------------------------------------------------
\r
1521 ------------------------------------------------------------------------------------------------------------------
\r
1522 ------------------------------------------------------------------------------------------------------------------
\r
1525 CREATE DATABASE [GHTDB_EX] ON (NAME = N'GHTDB_dat_EX', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\data\GHTDB_EX.mdf' , SIZE = 6, FILEGROWTH = 10%) LOG ON (NAME = N'GHTDB_EX_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\data\GHTDB_EX.ldf' , SIZE = 2, FILEGROWTH = 10%)
\r
1526 COLLATE SQL_Latin1_General_CP1_CI_AS
\r
1529 exec sp_dboption N'GHTDB_EX', N'autoclose', N'false'
\r
1532 exec sp_dboption N'GHTDB_EX', N'bulkcopy', N'true'
\r
1535 exec sp_dboption N'GHTDB_EX', N'trunc. log', N'true'
\r
1538 exec sp_dboption N'GHTDB_EX', N'torn page detection', N'true'
\r
1541 exec sp_dboption N'GHTDB_EX', N'read only', N'false'
\r
1544 exec sp_dboption N'GHTDB_EX', N'dbo use', N'false'
\r
1547 exec sp_dboption N'GHTDB_EX', N'single', N'false'
\r
1550 exec sp_dboption N'GHTDB_EX', N'autoshrink', N'false'
\r
1553 exec sp_dboption N'GHTDB_EX', N'ANSI null default', N'false'
\r
1556 exec sp_dboption N'GHTDB_EX', N'recursive triggers', N'false'
\r
1559 exec sp_dboption N'GHTDB_EX', N'ANSI nulls', N'false'
\r
1562 exec sp_dboption N'GHTDB_EX', N'concat null yields null', N'false'
\r
1565 exec sp_dboption N'GHTDB_EX', N'cursor close on commit', N'false'
\r
1568 exec sp_dboption N'GHTDB_EX', N'default to local cursor', N'false'
\r
1571 exec sp_dboption N'GHTDB_EX', N'quoted identifier', N'false'
\r
1574 exec sp_dboption N'GHTDB_EX', N'ANSI warnings', N'false'
\r
1577 exec sp_dboption N'GHTDB_EX', N'auto create statistics', N'true'
\r
1580 exec sp_dboption N'GHTDB_EX', N'auto update statistics', N'true'
\r
1583 if( ( (@@microsoftversion / power(2, 24) = 8) and (@@microsoftversion & 0xffff >= 724) ) or ( (@@microsoftversion / power(2, 24) = 7) and (@@microsoftversion & 0xffff >= 1082) ) )
\r
1584 exec sp_dboption N'GHTDB_EX', N'db chaining', N'false'
\r
1591 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GH_DUMMY]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
\r
1592 drop procedure [dbo].[GH_DUMMY]
\r
1595 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Customers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
\r
1596 drop table [dbo].[Customers]
\r
1599 CREATE TABLE [dbo].[Customers] (
\r
1600 [CustomerID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
\r
1601 [CompanyName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
\r
1602 [ContactName] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
\r
1603 [ContactTitle] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
\r
1604 [Address] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
\r
1605 [City] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
\r
1606 [Region] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
\r
1607 [PostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
\r
1608 [Country] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
\r
1609 [Phone] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
\r
1610 [Fax] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
\r
1614 SET QUOTED_IDENTIFIER OFF
\r
1616 SET ANSI_NULLS OFF
\r
1619 print '------------------------------'
\r
1620 print 'create another GH_DUMMY which select from a different table'
\r
1621 print 'customers instead of employees'
\r
1622 print '------------------------------'
\r
1625 CREATE procedure GH_DUMMY
\r
1626 @CustomerIdPrm char (10)
\r
1628 SELECT * FROM Customers where CustomerID = @CustomerIdPrm
\r
1630 SET QUOTED_IDENTIFIER OFF
\r
1632 SET ANSI_NULLS ON
\r