BEGIN TRY
BEGIN TRANSACTION
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].InvoiceItem') AND type in (N'U'))
drop table InvoiceItem
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].Invoice') AND type in (N'U'))
drop table Invoice
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].InvoiceIssuer') AND type in (N'U'))
drop table InvoiceIssuer
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].OrderItem') AND type in (N'U'))
drop table OrderItem
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Order]') AND type in (N'U'))
drop table [Order]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].Product') AND type in (N'U'))
drop table Product
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].Category') AND type in (N'U'))
drop table Category
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].VAT') AND type in (N'U'))
drop table VAT
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].PaymentMethod') AND type in (N'U'))
drop table PaymentMethod
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].Status') AND type in (N'U'))
drop table Status
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].Customer') AND type in (N'U'))
alter table Customer drop constraint Customer_MainCustomerSite
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].CustomerSite') AND type in (N'U'))
drop table CustomerSite
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].Customer') AND type in (N'U'))
drop table Customer
--*****************************************************************
-- Create tables
--*****************************************************************
create table [VAT]
(
ID int identity primary key,
Percentage int
)
create table [PaymentMethod]
(
ID int identity primary key,
Method nvarchar(20),
Deadline int
)
create table [Status]
(
ID int identity primary key,
Name nvarchar(20)
)
create table [Category]
(
ID int identity primary key,
Name nvarchar(50),
ParentCategoryID int references Category(ID)
)
create table Product
(
ID int identity primary key,
[Name] nvarchar(50),
Price float,
Stock int,
VATID int references VAT(ID),
CategoryID int references Category(ID),
[Description] XML
)
create table [Customer]
(
ID int identity primary key,
[Name] nvarchar(50),
BankAccount varchar(50),
[Login] nvarchar(50),
[Password] nvarchar(50),
Email varchar(50),
MainCustomerSiteID int
)
create table [CustomerSite]
(
ID int identity primary key,
ZipCode char(4),
City nvarchar(50),
Street nvarchar(50),
Tel varchar(15),
Fax varchar(15),
CustomerID int references Customer(ID)
)
alter table [Customer] add constraint Customer_MainCustomerSite foreign key (MainCustomerSiteID) references CustomerSite(ID)
create table [Order]
(
ID int identity primary key,
[Date] datetime,
Deadline datetime,
CustomerSiteID int references CustomerSite(ID),
StatusID int references Status(ID),
PaymentMethodID int references PaymentMethod(ID)
)
create table [OrderItem]
(
ID int identity primary key,
Amount int,
Price float,
OrderID int references [Order](ID),
ProductID int references Product(ID),
StatusID int references Status(ID)
)
create table [InvoiceIssuer]
(
ID int identity primary key,
[Name] nvarchar(50),
ZipCode char(4),
City nvarchar(50),
Street nvarchar(50),
TaxIdentifier varchar(20),
BankAccount varchar(50)
)
create table [Invoice]
(
ID int primary key,
CustomerName nvarchar(50),
CustomerZipCode char(4),
CustomerCity nvarchar(50),
CustomerStreet nvarchar(50),
PrintedCopies int,
Cancelled bit,
PaymentMethod nvarchar(20),
CreationDate datetime,
DeliveryDate datetime,
PaymentDeadline datetime,
InvoiceIssuerID int references InvoiceIssuer(ID),
OrderID int references [Order](ID)
)
create table [InvoiceItem]
(
ID int identity primary key,
[Name] nvarchar(50),
Amount int,
Price float,
VATPercentage int,
InvoiceID int references Invoice(ID),
OrderItemID int references OrderItem(ID)
)
--*****************************************************************
-- Insert sample data
--*****************************************************************
SET IDENTITY_INSERT VAT ON
insert into VAT(id, Percentage) values (1,0);
insert into VAT(id, Percentage) values (2,15);
insert into VAT(id, Percentage) values (3,27);
SET IDENTITY_INSERT VAT OFF
SET IDENTITY_INSERT Category ON
insert into Category (id, Name, ParentCategoryID) values (1,'Toy',NULL);
insert into Category (id, Name, ParentCategoryID) values (2,'Play house',NULL);
insert into Category (id, Name, ParentCategoryID) values (3,'Baby toy',1);
insert into Category (id, Name, ParentCategoryID) values (4,'Construction toy',1);
insert into Category (id, Name, ParentCategoryID) values (5,'Wooden toy',1);
insert into Category (id, Name, ParentCategoryID) values (6,'Plush figure',1);
insert into Category (id, Name, ParentCategoryID) values (7,'Bicycles',1);
insert into Category (id, Name, ParentCategoryID) values (8,'Months 0-6',3);
insert into Category (id, Name, ParentCategoryID) values (9,'Months 6-18',3);
insert into Category (id, Name, ParentCategoryID) values (10,'Months 18-24',3);
insert into Category (id, Name, ParentCategoryID) values (11,'DUPLO',4);
insert into Category (id, Name, ParentCategoryID) values (13,'LEGO',4);
insert into Category (id, Name, ParentCategoryID) values (14,'Building items',4);
insert into Category (id, Name, ParentCategoryID) values (15,'Building blocks',5);
insert into Category (id, Name, ParentCategoryID) values (16,'Toys for skill development',5);
insert into Category (id, Name, ParentCategoryID) values (17,'Logic toys',5);
insert into Category (id, Name, ParentCategoryID) values (18,'Craftwork toys',5);
insert into Category (id, Name, ParentCategoryID) values (19,'Baby taxis',7);
insert into Category (id, Name, ParentCategoryID) values (20,'Motors',7);
insert into Category (id, Name, ParentCategoryID) values (21,'Tricycle',7);
SET IDENTITY_INSERT Category OFF
SET IDENTITY_INSERT Product ON
insert into Product (id, Name, Price,Stock,VATid,Categoryid) values (1,'Activity playgim',7488, 21, 3, 8);
insert into Product (id, Name, Price,Stock,VATid,Categoryid) values (2,'Colorful baby book',1738, 58, 3, 8);
insert into Product (id, Name, Price,Stock,VATid,Categoryid) values (3,'Baby telephone',3725, 18, 3, 9);
insert into Product (id, Name, Price,Stock,VATid,Categoryid) values (4,'Fisher Price hammer toy',8356, 58, 3, 10);
insert into Product (id, Name, Price,Stock,VATid,Categoryid) values (5,'Mega Bloks 24 pcs',4325, 47, 3, 14);
insert into Product (id, Name, Price,Stock,VATid,Categoryid) values (6,'Maxi Blocks 56 pcs',1854, 36, 3, 14);
insert into Product (id, Name, Price,Stock,VATid,Categoryid) values (7,'Building Blocks 80 pcs',4362, 25, 3, 14);
insert into Product (id, Name, Price,Stock,VATid,Categoryid) values (8,'Lego City harbour',27563, 12, 3, 13);
insert into Product (id, Name, Price,Stock,VATid,Categoryid) values (9,'Lego Duplo Excavator',6399, 26, 3, 11);
insert into Product (id, Name, Price,Stock,VATid,Categoryid) values (10,'Child supervision for 1 hour',800, 0, 2, 2);
SET IDENTITY_INSERT Product OFF
SET IDENTITY_INSERT CustomerSite ON
insert into CustomerSite (id, ZipCode, City, Street, tel, fax, Customerid)
values (1,'1114','Budapest','Bud Spencer street 16.','061-569-23-99',null,null);
insert into CustomerSite (id, ZipCode, City, Street, tel, fax, Customerid)
values (2,'1051','Budapest','Andrássy út 22.','061-457-11-03','061-457-11-04',null);
insert into CustomerSite (id, ZipCode, City, Street, tel, fax, Customerid)
values (3,'3000','Hatvan','Vörösmarty tér. 5.','0646-319-169','0646-319-168',null);
insert into CustomerSite (id, ZipCode, City, Street, tel, fax, Customerid)
values (4,'2045','Törökbálint','Main street 17.','0623-200-156','0623-200-155',null);
SET IDENTITY_INSERT CustomerSite OFF
SET IDENTITY_INSERT Customer ON
insert into Customer (id, Name, BankAccount,login, Password,email,MainCustomerSiteId)
values (1,'Cody Shelton','16489665-05899845-10000038','cshelton','huti9haj1s','cshelton@freemail.hu',2);
update CustomerSite set Customerid =1 where id = 2;
insert into Customer (id, Name, BankAccount,login, Password,email,MainCustomerSiteId)
values (2,'Erika Mckenzie','54255831-15615432-25015126','erikkka','gandalf67j','erikkka@hotmail.com',1);
update CustomerSite set Customerid=2 where id = 1;
update CustomerSite set Customerid=2 where id = 3;
insert into Customer (id, Name, BankAccount,login, Password,email,MainCustomerSiteId)
values (3,'Krista Hansen','25894467-12005362-59815126','kris','jag7guFs','kris.hansen@gmail.com',4);
update CustomerSite set Customerid =3 where id = 4;
SET IDENTITY_INSERT Customer OFF
SET IDENTITY_INSERT InvoiceIssuer ON
insert into InvoiceIssuer (id, Name, ZipCode, City, Street, TaxIdentifier, BankAccount)
values (1,'ToysRus','1119','Budapest','Main street 23','15684995-2-32','259476332-15689799-10020065');
insert into InvoiceIssuer (id, Name, ZipCode, City, Street, TaxIdentifier, BankAccount)
values (2,'BabiesRus','1119','Budapest','Main street 23','68797867-1-32','259476332-15689799-10020065');
SET IDENTITY_INSERT InvoiceIssuer OFF
SET IDENTITY_INSERT Status ON
insert into Status (id, Name) values (1,'New');
insert into Status (id, Name) values (2,'Processing');
insert into Status (id, Name) values (3,'Packaged');
insert into Status (id, Name) values (4,'In transit');
insert into Status (id, Name) values (5,'Delivered');
SET IDENTITY_INSERT Status OFF
SET IDENTITY_INSERT PaymentMethod ON
insert into PaymentMethod (id, Method, Deadline) values (1,'Cash',0);
insert into PaymentMethod (id, Method, Deadline) values (2,'Wire transfer 8',8);
insert into PaymentMethod (id, Method, Deadline) values (3,'Wire transfer 15',15);
insert into PaymentMethod (id, Method, Deadline) values (4,'Wire transfer 30',30);
insert into PaymentMethod (id, Method, Deadline) values (5,'Credit card',0);
insert into PaymentMethod (id, Method, Deadline) values (6,'Collect package',0);
SET IDENTITY_INSERT PaymentMethod OFF
SET IDENTITY_INSERT [Order] ON
insert into [Order] (id, Date, Deadline, CustomerSiteid, Statusid, PaymentMethodid)
values (1,'2020-01-18','2020-01-30',3,5,1);
insert into [Order] (id, Date, Deadline, CustomerSiteid, Statusid, PaymentMethodid)
values (2,'2020-02-13','2020-02-15',2,5,2);
insert into [Order] (id, Date, Deadline, CustomerSiteid, Statusid, PaymentMethodid)
values (3,'2020-02-15','2020-02-20',1,2,1);
insert into [Order] (id, Date, Deadline, CustomerSiteid, Statusid, PaymentMethodid)
values (4,'2020-02-15','2020-02-20',2,3,5);
SET IDENTITY_INSERT [Order] OFF
SET IDENTITY_INSERT OrderItem ON
-- first [Order]
insert into OrderItem (id, Amount, Price,OrderID,Productid,Statusid)
values (1,2,8356,1,4,5);
insert into OrderItem (id, Amount, Price,OrderID,Productid,Statusid)
values (2,1,1854,1,6,5);
insert into OrderItem (id, Amount, Price,OrderID,Productid,Statusid)
values (3,5,1738,1,2,5);
-- second [Order]
insert into OrderItem (id, Amount, Price,OrderID,Productid,Statusid)
values (4,2,7488,2,1,5);
insert into OrderItem (id, Amount, Price,OrderID,Productid,Statusid)
values (5,3,3725,2,3,5);
-- third [Order]
insert into OrderItem (id, Amount, Price,OrderID,Productid,Statusid)
values (6,1,4362,3,7,3);
insert into OrderItem (id, Amount, Price,OrderID,Productid,Statusid)
values (7,6,1854,3,6,2);
insert into OrderItem (id, Amount, Price,OrderID,Productid,Statusid)
values (8,2,6399,3,9,3);
insert into OrderItem (id, Amount, Price,OrderID,Productid,Statusid)
values (9,5,1738,3,2,1);
-- forth [Order]
insert into OrderItem (id, Amount, Price,OrderID,Productid,Statusid)
values (10,23,3725,4,3,3);
insert into OrderItem (id, Amount, Price,OrderID,Productid,Statusid)
values (11,12,1738,4,2,3);
insert into OrderItem (id, Amount, Price,OrderID,Productid,Statusid)
values (12,10,27563,4,8,3);
insert into OrderItem (id, Amount, Price,OrderID,Productid,Statusid)
values (13,25,7488,4,1,3);
SET IDENTITY_INSERT OrderItem OFF
insert into Invoice (id, CustomerName, CustomerZipCode,CustomerCity,CustomerStreet,PrintedCopies,
Cancelled,PaymentMethod,CreationDate,DeliveryDate,PaymentDeadline,InvoiceIssuerID,OrderID)
values (1,'Erika Mckenzie','3000','Hatvan','Second street 5.',2,0,'Cash','2008-01-30','2008-01-30','2008-01-30',1,1);
insert into Invoice (id, CustomerName, CustomerZipCode,CustomerCity,CustomerStreet,PrintedCopies,
Cancelled,PaymentMethod,CreationDate,DeliveryDate,PaymentDeadline,InvoiceIssuerID,OrderID)
values (2,'Cody Shelton','1051','Budapest','First street 22.',2,0,'Wire transfer 8','2008-02-14','2008-02-15','2008-02-23',1,2);
SET IDENTITY_INSERT InvoiceItem ON
insert into InvoiceItem (id, Name, Amount, Price, VATPercentage,Invoiceid, OrderItemid)
values (1,'Fisher Price hammer',2,8356,27,1,1);
insert into InvoiceItem (id, Name, Amount, Price, VATPercentage,Invoiceid, OrderItemid)
values (2,'Maxi Blocks 56 pcs',1,1854,27,1,2);
insert into InvoiceItem (id, Name, Amount, Price, VATPercentage,Invoiceid, OrderItemid)
values (3,'Colorful baby book',5,1738,27,1,3);
insert into InvoiceItem (id, Name, Amount, Price, VATPercentage,Invoiceid, OrderItemid)
values (4,'Activity playgim',2,7488,27,2,4);
insert into InvoiceItem (id, Name, Amount, Price, VATPercentage,Invoiceid, OrderItemid)
values (5,'Baby telephone',3,3725,27,2,5);
SET IDENTITY_INSERT InvoiceItem OFF
--*****************************************************************
-- XML descriptions
--*****************************************************************
update Product set Description =
'
cm
150
50
150
1
cm
150
20
20
Requires battery (not part of the package).
0-18 m
'
where id = 1
update Product set Description =
'
cm
15
2
15
1
cm
15
2
15
Round ball with nice colors.
0-18 m
'
where id = 2
update Product set Description =
'
cm
20
12
35
1
cm
40
25
50
Music is good for the ears. Enjoy.
9-36 m
'
where id = 3
update Product set Description =
'
1
cm
80
20
40
Number of elements: 695.
5-12 y
'
where id = 8
IF @@Trancount >0
commit
END TRY
BEGIN CATCH
IF @@Trancount >0
rollback
IF CURSOR_STATUS('global','cur') >= -1
deallocate cur
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() as ErrorState,
ERROR_PROCEDURE() as ErrorProcedure,
ERROR_LINE() as ErrorLine,
ERROR_MESSAGE() as ErrorMessage
END CATCH