make-tables.sql

ASP.NET Web Forms Techniques

GRANT  EXECUTE  ON [dbo].[GetNewsList]  TO [public]
GO

GRANT  EXECUTE  ON [dbo].[GetNewsList]  TO [anon]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

/****** Object:  Stored Procedure dbo.GetUserDetails    Script Date: 13/08/2002 15:51:21 ******/
CREATE PROCEDURE GetUserDetails
@UserID varchar(20)
AS
SELECT * FROM tblUsers WHERE UserID = @UserID

GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

GRANT  EXECUTE  ON [dbo].[GetUserDetails]  TO [public]
GO

GRANT  EXECUTE  ON [dbo].[GetUserDetails]  TO [anon]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO

/****** Object:  Stored Procedure dbo.InsertNewUser    Script Date: 13/08/2002 15:51:21 ******/
CREATE PROCEDURE InsertNewUser
@UserID varchar(20), @UserPW varchar(255),
@UserName varchar(50), @Address varchar(60),
@City varchar(30), @State varchar(20),
@Country varchar(30), @Phone varchar(20),
@Email varchar(50)
AS
INSERT INTO tblUsers(UserID, UserPW, UserName, Address, City, State, Country, Phone, Email)
VALUES (@UserID, @UserPW, @UserName, @Address, @City, @State, @Country, @Phone, @Email)

GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

GRANT  EXECUTE  ON [dbo].[InsertNewUser]  TO [public]
GO

GRANT  EXECUTE  ON [dbo].[InsertNewUser]  TO [anon]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO

/****** Object:  Stored Procedure dbo.UpdatePmtFinanceData    Script Date: 13/08/2002 15:51:21 ******/
CREATE PROCEDURE UpdatePmtFinanceData
@Months int,
@Payment decimal(9,2)
AS
UPDATE tblFinancePMTData SET Payment = @Payment WHERE Months = @Months
IF @@ROWCOUNT = 0
    INSERT INTO tblFinancePMTData (Months, Payment) VALUES (@Months, @Payment)

GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

GRANT  EXECUTE  ON [dbo].[UpdatePmtFinanceData]  TO [public]
GO

GRANT  EXECUTE  ON [dbo].[UpdatePmtFinanceData]  TO [anon]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

/****** Object:  Stored Procedure dbo.ConvertToOrder    Script Date: 13/08/2002 15:51:21 ******/
CREATE PROCEDURE ConvertToOrder
@QuoteOrderID int
AS
UPDATE tblQuoteOrder SET IsOrder = 1 WHERE QuoteOrderID = @QuoteOrderID

GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

GRANT  EXECUTE  ON [dbo].[ConvertToOrder]  TO [public]
GO

GRANT  EXECUTE  ON [dbo].[ConvertToOrder]  TO [anon]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO

/****** Object:  Stored Procedure dbo.GetAllCarColors    Script Date: 13/08/2002 15:51:21 ******/
CREATE PROCEDURE GetAllCarColors
@CarID int
AS
SELECT tblCarColors.ColorID, tblColor.Color, tblColor.IsMetallic
  FROM (tblCar JOIN tblCarColors ON tblCar.CarID = tblCarColors.CarID)
  JOIN tblColor ON tblCarColors.ColorID = tblColor.ColorID
  WHERE tblCar.CarID = @CarID

GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

GRANT  EXECUTE  ON [dbo].[GetAllCarColors]  TO [public]
GO

GRANT  EXECUTE  ON [dbo].[GetAllCarColors]  TO [anon]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO

/****** Object:  Stored Procedure dbo.GetAllCarDetails    Script Date: 13/08/2002 15:51:21 ******/
CREATE PROCEDURE GetAllCarDetails
@CarID int
AS
SELECT tblCar.CarID, Model, Precis, tblEngine.EngineID, EngineName,
  CubicCapacity, FuelType, Cylinders, MaxRPM, BHP, Gears, CarEnginePrice,
  Turbo = CASE Turbo WHEN 1 THEN 'Yes' ELSE 'No' END,
  AutoTrans = CASE AutoTrans WHEN 1 THEN 'Yes' ELSE 'No' END
FROM (tblCar JOIN tblCarEngines ON tblCar.CarID = tblCarEngines.CarID)
  JOIN tblEngine ON tblEngine.EngineID = tblCarEngines.EngineID
WHERE tblCar.CarID = @CarID

GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

GRANT  EXECUTE  ON [dbo].[GetAllCarDetails]  TO [public]
GO

GRANT  EXECUTE  ON [dbo].[GetAllCarDetails]  TO [anon]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO

/****** Object:  Stored Procedure dbo.GetAllCarExtras    Script Date: 13/08/2002 15:51:21 ******/
CREATE PROCEDURE GetAllCarExtras
@CarID int
AS
SELECT tblOptionExtra.ExtraID, tblOptionExtra.ExtraText, tblOptionExtra.ExtraPrice,
               DisplayText = ' ' +  RTRIM(tblOptionExtra.ExtraText) + ' ($' + CONVERT(varchar(10), tblOptionExtra.ExtraPrice) + ')   '
  FROM (tblCar JOIN tblCarExtras ON tblCar.CarID = tblCarExtras.CarID)
  JOIN tblOptionExtra ON tblCarExtras.ExtraID = tblOptionExtra.ExtraID
  WHERE tblCar.CarID = @CarID

GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

GRANT  EXECUTE  ON [dbo].[GetAllCarExtras]  TO [public]
GO

GRANT  EXECUTE  ON [dbo].[GetAllCarExtras]  TO [anon]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO

/****** Object:  Stored Procedure dbo.GetModelEngineCombinedList    Script Date: 13/08/2002 15:51:21 ******/
CREATE PROCEDURE GetModelEngineCombinedList AS
SELECT ViewName = tblCar.Model + ' ' + tblEngine.EngineName
FROM (tblCar JOIN tblCarEngines ON tblCar.CarID = tblCarEngines.CarID)
  JOIN tblEngine ON tblEngine.EngineID = tblCarEngines.EngineID
ORDER BY tblCar.Model, tblEngine.EngineName

GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

GRANT  EXECUTE  ON [dbo].[GetModelEngineCombinedList]  TO [public]
GO

GRANT  EXECUTE  ON [dbo].[GetModelEngineCombinedList]  TO [anon]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

/****** Object:  Stored Procedure dbo.GetModelList    Script Date: 13/08/2002 15:51:21 ******/
CREATE PROCEDURE GetModelList AS
SELECT CarID, Model, Precis,
  (SELECT MIN(CarEnginePrice) FROM tblCarEngines WHERE tblCarEngines.CarID=tblCar.CarID) As Price
FROM tblCar
ORDER BY Model

GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

GRANT  EXECUTE  ON [dbo].[GetModelList]  TO [public]
GO

GRANT  EXECUTE  ON [dbo].[GetModelList]  TO [anon]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

/****** Object:  Stored Procedure dbo.GetUserOrders    Script Date: 13/08/2002 15:51:21 ******/
CREATE PROCEDURE GetUserOrders
@UserID varchar(20)
AS
SELECT * FROM tblQuoteOrder WHERE UserID = @UserID
ORDER BY OrderDate DESC

GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

GRANT  EXECUTE  ON [dbo].[GetUserOrders]  TO [public]
GO

GRANT  EXECUTE  ON [dbo].[GetUserOrders]  TO [anon]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

/****** Object:  Stored Procedure dbo.InsertNewQuote    Script Date: 13/08/2002 15:51:22 ******/
CREATE PROCEDURE InsertNewQuote
@UserID varchar(20), @CarID int, @CarName varchar(50), @EngineID int,
@EngineName varchar(10), @ColorID int, @ColorName varchar(50),
@BasePrice money, @Interest money, @TotalPrice money,
@PaymentMonths money, @PaymentAmount money,
@QuoteOrderID int OUTPUT
AS
INSERT INTO tblQuoteOrder(UserID, CarID, CarName, EngineID,
   EngineName, ColorID, ColorName, BasePrice, Interest,
   TotalPrice, PaymentMonths, PaymentAmount)
VALUES (@UserID, @CarID, @CarName, @EngineID,
   @EngineName, @ColorID, @ColorName, @BasePrice, @Interest,
   @TotalPrice, @PaymentMonths, @PaymentAmount)
SELECT @QuoteOrderID = @@IDENTITY

GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

GRANT  EXECUTE  ON [dbo].[InsertNewQuote]  TO [public]
GO

GRANT  EXECUTE  ON [dbo].[InsertNewQuote]  TO [anon]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO

/****** Object:  Stored Procedure dbo.GetUserOrderLines    Script Date: 13/08/2002 15:51:22 ******/
CREATE PROCEDURE GetUserOrderLines
@UserID varchar(20)
AS
SELECT tblQuoteOrder.QuoteOrderID, ExtraName, ExtraPrice
FROM tblQuoteOrder JOIN tblQuoteOrderExtras
  ON tblQuoteOrder.QuoteOrderID = tblQuoteOrderExtras.QuoteOrderID
WHERE UserID = @UserID

GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

GRANT  EXECUTE  ON [dbo].[GetUserOrderLines]  TO [public]
GO

GRANT  EXECUTE  ON [dbo].[GetUserOrderLines]  TO [anon]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO

/****** Object:  Stored Procedure dbo.InsertNewQuoteExtraLine    Script Date: 13/08/2002 15:51:22 ******/
CREATE PROCEDURE InsertNewQuoteExtraLine
@QuoteOrderID int, @ExtraID int, @ExtraName varchar(50), @ExtraPrice money
AS
INSERT INTO tblQuoteOrderExtras(QuoteOrderID, ExtraID, ExtraName, ExtraPrice)
VALUES (@QuoteOrderID, @ExtraID, @ExtraName, @ExtraPrice)

GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

GRANT  EXECUTE  ON [dbo].[InsertNewQuoteExtraLine]  TO [public]
GO

GRANT  EXECUTE  ON [dbo].[InsertNewQuoteExtraLine]  TO [anon]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

/****** Object:  Stored Procedure dbo.MigrateQuotes    Script Date: 13/08/2002 15:51:22 ******/
CREATE PROCEDURE MigrateQuotes
@AnonUserID varchar(20), @NewUserID varchar(20)
AS
UPDATE tblQuoteOrder SET UserID = @NewUserID
  WHERE UserID = @AnonUserID

GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

GRANT  EXECUTE  ON [dbo].[MigrateQuotes]  TO [public]
GO

GRANT  EXECUTE  ON [dbo].[MigrateQuotes]  TO [anon]
GO