make_tables.sql

This is a book about vb.you could learn this from this book

/****** Object:  Table [dbo].[BookAuthors]    Script Date: 19/01/2001 11:14:00 ******/
if exists (select * from sysobjects where id = object_id(N'[dbo].[BookAuthors]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[BookAuthors]
GO

/****** Object:  Table [dbo].[BookList]    Script Date: 19/01/2001 11:14:00 ******/
if exists (select * from sysobjects where id = object_id(N'[dbo].[BookList]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[BookList]
GO

/****** Object:  Table [dbo].[BookPrices]    Script Date: 19/01/2001 11:14:00 ******/
if exists (select * from sysobjects where id = object_id(N'[dbo].[BookPrices]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[BookPrices]
GO

/****** Object:  Table [dbo].[ArchiveBooks]    Script Date: 19/01/2001 11:14:00 ******/
if exists (select * from sysobjects where id = object_id(N'[dbo].[ArchiveBooks]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ArchiveBooks]
GO

/****** Object:  Login anon    Script Date: 19/01/2001 11:13:57 ******/
if not exists (select * from master..syslogins where name = N'anon')
BEGIN
   declare @logindb nvarchar(132), @loginlang nvarchar(132) select @logindb = N'WroxBooks', @loginlang = N'us_english'
   if @logindb is null or not exists (select * from master..sysdatabases where name = @logindb)
      select @logindb = N'master'
   if @loginlang is null or (not exists (select * from master..syslanguages where name = @loginlang) and @loginlang <> N'us_english')
      select @loginlang = @@language
   exec sp_addlogin N'anon', null, @logindb, @loginlang
END
GO

/****** Object:  User anon    Script Date: 19/01/2001 11:13:57 ******/
if not exists (select * from sysusers where name = N'anon' and uid < 16382)
   EXEC sp_grantdbaccess N'anon', N'anon'
GO

/****** Object:  Table [dbo].[BookAuthors]    Script Date: 19/01/2001 11:14:04 ******/
CREATE TABLE [dbo].[BookAuthors] (
   [ISBN] [nvarchar] (10) NOT NULL ,
   [FirstName] [nvarchar] (50) NULL ,
   [LastName] [nvarchar] (50) NULL
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[BookList]    Script Date: 19/01/2001 11:14:06 ******/
CREATE TABLE [dbo].[BookList] (
   [ISBN] [nvarchar] (10) NOT NULL ,
   [Title] [nvarchar] (255) NOT NULL ,
   [PublicationDate] [datetime] NULL
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[BookPrices]    Script Date: 19/01/2001 11:14:06 ******/
CREATE TABLE [dbo].[BookPrices] (
   [ISBN] [nvarchar] (10) NOT NULL ,
   [Currency] [nvarchar] (3) NOT NULL ,
   [Price] [money] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[BookList] WITH NOCHECK ADD
   CONSTRAINT [PK_BookList] PRIMARY KEY  NONCLUSTERED
   (
      [ISBN]
   )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[BookPrices] WITH NOCHECK ADD
   CONSTRAINT [PK_BookPrices] PRIMARY KEY  NONCLUSTERED
   (
      [ISBN],
      [Currency]
   )  ON [PRIMARY]
GO


/****** Object:  Table [dbo].[ArchiveBooks]    Script Date: 19/01/2001 11:14:06 ******/
CREATE TABLE [dbo].[ArchiveBooks] (
   [ISBN] [nvarchar] (10) NOT NULL ,
   [Title] [nvarchar] (255) NOT NULL ,
   [PublicationDate] [datetime] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ArchiveBooks] WITH NOCHECK ADD
   CONSTRAINT [PK_ArchiveBooks] PRIMARY KEY  NONCLUSTERED
   (
      [ISBN]
   )  ON [PRIMARY]
GO

 CREATE  INDEX [IX_BookList_Title] ON [dbo].[BookList]([Title]) ON [PRIMARY]
GO
GRANT  REFERENCES ,  SELECT ,  INSERT ,  DELETE ,  UPDATE  ON [dbo].[BookAuthors]  TO [public]
GO
GRANT  REFERENCES ,  SELECT ,  INSERT ,  DELETE ,  UPDATE  ON [dbo].[BookList]  TO [public]
GO
GRANT  REFERENCES ,  SELECT ,  INSERT ,  DELETE ,  UPDATE  ON [dbo].[BookPrices]  TO [public]
GO
GRANT  REFERENCES ,  SELECT ,  INSERT ,  DELETE ,  UPDATE  ON [dbo].[BookAuthors]  TO [anon]
GO
GRANT  REFERENCES ,  SELECT ,  INSERT ,  DELETE ,  UPDATE  ON [dbo].[BookList]  TO [anon]
GO
GRANT  REFERENCES ,  SELECT ,  INSERT ,  DELETE ,  UPDATE  ON [dbo].[BookPrices]  TO [anon]
GO
GRANT  REFERENCES ,  SELECT ,  INSERT ,  DELETE ,  UPDATE  ON [dbo].[ArchiveBooks]  TO [public]
GO
GRANT  REFERENCES ,  SELECT ,  INSERT ,  DELETE ,  UPDATE  ON [dbo].[ArchiveBooks]  TO [anon]
GO

/****** Object:  Stored Procedure dbo.FindFromTitleAndDate    Script Date: 14/02/2001 16:33:29 ******/
if exists (select * from sysobjects where id = object_id(N'[dbo].[FindFromTitleAndDate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[FindFromTitleAndDate]
GO

/****** Object:  Stored Procedure dbo.FindFromISBN    Script Date: 14/02/2001 16:33:29 ******/
if exists (select * from sysobjects where id = object_id(N'[dbo].[FindFromISBN]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[FindFromISBN]
GO

/****** Object:  Stored Procedure dbo.BookAuthorDelete    Script Date: 18/02/2001 11:34:50 ******/
if exists (select * from sysobjects where id = object_id(N'[dbo].[BookAuthorDelete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[BookAuthorDelete]
GO

/****** Object:  Stored Procedure dbo.BookAuthorInsert    Script Date: 18/02/2001 11:34:50 ******/
if exists (select * from sysobjects where id = object_id(N'[dbo].[BookAuthorInsert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[BookAuthorInsert]
GO

/****** Object:  Stored Procedure dbo.BookAuthorUpdate    Script Date: 18/02/2001 11:34:50 ******/
if exists (select * from sysobjects where id = object_id(N'[dbo].[BookAuthorUpdate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[BookAuthorUpdate]
GO
SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON
GO

/****** Object:  Stored Procedure dbo.AddNewBook    Script Date: 28/02/2001 15:08:30 ******/
if exists (select * from sysobjects where id = object_id(N'[dbo].[AddNewBook]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[AddNewBook]
GO

/****** Object:  Stored Procedure dbo.GetBooks    Script Date: 14/02/2001 16:33:29 ******/
if exists (select * from sysobjects where id = object_id(N'[dbo].[GetBooks]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetBooks]
GO
SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON
GO

/****** Object:  Stored Procedure dbo.DoBookArchive    Script Date: 28/02/2001 16:13:57 ******/
if exists (select * from sysobjects where id = object_id(N'[dbo].[DoBookArchive]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DoBookArchive]
GO
SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON
GO

/****** Object:  Stored Procedure dbo.FindFromTitleAndDate    Script Date: 14/02/2001 16:33:29 ******/
CREATE PROCEDURE FindFromTitleAndDate
@Title varchar(50),
@Date datetime
AS
SELECT *  FROM BookList WHERE(Title LIKE @Title) AND (PublicationDate = @Date)
GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON
GO
GRANT  EXECUTE  ON [dbo].[FindFromTitleAndDate] TO [anon]
GO
GRANT  EXECUTE  ON [dbo].[FindFromTitleAndDate] TO [public]
GO
SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON
GO

/****** Object:  Stored Procedure dbo.FindFromISBN    Script Date: 14/02/2001 16:33:30 ******/
CREATE PROCEDURE FindFromISBN
@ISBN varchar(12),
@Title varchar(50) output,
@Date datetime output
AS
SELECT @Title = Title, @Date = PublicationDate FROM BookList WHERE ISBN = @ISBN
GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON
GO
GRANT  EXECUTE  ON [dbo].[FindFromISBN] TO [anon]
GO
GRANT  EXECUTE  ON [dbo].[FindFromISBN] TO [public]
GO

/****** Object:  Stored Procedure dbo.GetBooks    Script Date: 14/02/2001 16:33:30 ******/
CREATE PROCEDURE GetBooks
AS
SELECT * FROM BookList WHERE Title LIKE '% ASP %'
GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON
GO
GRANT  EXECUTE  ON [dbo].[GetBooks]  TO [public]
GO
GRANT  EXECUTE  ON [dbo].[GetBooks]  TO [anon]
GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON
GO
/****** Object:  Stored Procedure dbo.BookAuthorDelete    Script Date: 18/02/2001 11:34:50 ******/
CREATE PROCEDURE BookAuthorDelete
@ISBN varchar(12),
@FirstName varchar(50),
@LastName varchar(50)
AS
DELETE FROM BookAuthors WHERE ISBN=@ISBN AND FirstName=@FirstName AND LastName=@LastName
SELECT ISBN FROM BookAuthors WHERE ISBN=@ISBN
IF @@ROWCOUNT = 0
  BEGIN
    DELETE FROM BookList WHERE ISBN=@ISBN
  END
GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON
GO
GRANT  EXECUTE  ON [dbo].[BookAuthorDelete]  TO [anon]
GO
GRANT  EXECUTE  ON [dbo].[GetBooks]  TO [public]
GO
SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON
GO

/****** Object:  Stored Procedure dbo.BookAuthorInsert    Script Date: 18/02/2001 11:34:51 ******/
CREATE PROCEDURE BookAuthorInsert
@ISBN varchar(12),
@Title varchar(100),
@PublicationDate datetime,
@FirstName varchar(50),
@LastName varchar(50)
AS
SELECT ISBN FROM BookList WHERE ISBN=@ISBN
IF @@ROWCOUNT = 0
  BEGIN
    INSERT INTO BookList(ISBN, Title, PublicationDate) VALUES (@ISBN, @Title, @PublicationDate)
  END
INSERT INTO BookAuthors(ISBN, FirstName, LastName) VALUES (@ISBN, @FirstName, @LastName)
GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON
GO
GRANT  EXECUTE  ON [dbo].[BookAuthorInsert]  TO [anon]
GO
GRANT  EXECUTE  ON [dbo].[GetBooks]  TO [public]
GO
SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON
GO

/****** Object:  Stored Procedure dbo.BookAuthorUpdate    Script Date: 18/02/2001 11:34:51 ******/
CREATE PROCEDURE BookAuthorUpdate
@ISBN varchar(12),
@Title varchar(100),
@PublicationDate datetime,
@FirstName varchar(50),
@LastName varchar(50)
AS
UPDATE BookList SET Title=@Title, PublicationDate=@PublicationDate WHERE ISBN=@ISBN
UPDATE BookAuthors SET FirstName=@FirstName, LastName=@LastName WHERE ISBN=@ISBN
GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON
GO
GRANT  EXECUTE  ON [dbo].[BookAuthorUpdate]  TO [anon]
GO
GRANT  EXECUTE  ON [dbo].[GetBooks]  TO [public]
GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON
GO

/****** Object:  Stored Procedure dbo.AddNewBook    Script Date: 28/02/2001 15:08:30 ******/
CREATE PROCEDURE AddNewBook
@ISBN varchar(12),
@Title varchar(100),
@Date datetime,
@Result integer output
AS
SELECT ISBN FROM BookList WHERE ISBN=@ISBN
IF @@ROWCOUNT = 0
  BEGIN
    INSERT INTO BookList(ISBN, Title, PublicationDate) VALUES (@ISBN, @Title, @Date)
    SELECT @Result = 0
  END
ELSE
  BEGIN
    DELETE FROM BookList WHERE ISBN=@ISBN
    SELECT @Result = -1
  END
GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON
GO

GRANT  EXECUTE  ON [dbo].[AddNewBook]  TO [anon]
GO
GRANT  EXECUTE  ON [dbo].[AddNewBook]  TO [public]
GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON
GO

/****** Object:  Stored Procedure dbo.DoBookArchive    Script Date: 28/02/2001 16:13:57 ******/
CREATE PROCEDURE DoBookArchive
@ISBN varchar(12),
@Result integer output
AS
BEGIN TRANSACTION
DELETE FROM ArchiveBooks WHERE ISBN=@ISBN
INSERT INTO ArchiveBooks (ISBN, Title, PublicationDate)
SELECT * FROM BookList WHERE ISBN LIKE @ISBN
SELECT @Result = @@ROWCOUNT
IF @@ERROR <> 0 GOTO on_error
IF @Result > 0
  BEGIN
    DELETE FROM BookList WHERE ISBN=@ISBN
    IF @@ERROR <> 0 GOTO on_error
    COMMIT TRANSACTION
  END
ELSE
  ROLLBACK TRANSACTION
RETURN
on_error:
SELECT @Result = -1
ROLLBACK TRANSACTION
RETURN
GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON
GO
GRANT  EXECUTE  ON [dbo].[DoBookArchive]  TO [anon]
GO
GRANT  EXECUTE  ON [dbo].[DoBookArchive]  TO [public]
GO