easyobjectsquickstarts.sql

EasyObjects 是ORM的典型应用的例子是学习研究的很好的范例

		@OrderID,
		@ProductID,
		@UnitPrice,
		@Quantity,
		@Discount
	)

	SET @Err = @@Error

	RETURN @Err
END
GO


-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: daab_AddOrderDetails Succeeded'
ELSE PRINT 'Procedure Creation: daab_AddOrderDetails Error on Creation'
GO

IF EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID('daab_DeleteOrderDetails') AND sysstat & 0xf = 4)
    DROP PROCEDURE [daab_DeleteOrderDetails];
GO

CREATE PROCEDURE [daab_DeleteOrderDetails]
(
	@OrderID int,
	@ProductID int
)
AS
BEGIN

	SET NOCOUNT ON
	DECLARE @Err int

	DELETE
	FROM [Order Details]
	WHERE
		[OrderID] = @OrderID AND
		[ProductID] = @ProductID
	SET @Err = @@Error

	RETURN @Err
END
GO


-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: daab_DeleteOrderDetails Succeeded'
ELSE PRINT 'Procedure Creation: daab_DeleteOrderDetails Error on Creation'
GO

IF EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID('daab_GetOrders') AND sysstat & 0xf = 4)
    DROP PROCEDURE [daab_GetOrders];
GO

CREATE PROCEDURE [daab_GetOrders]
(
	@OrderID int
)
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @Err int

	SELECT
		[OrderID],
		[CustomerID],
		[EmployeeID],
		[OrderDate],
		[RequiredDate],
		[ShippedDate],
		[ShipVia],
		[Freight],
		[ShipName],
		[ShipAddress],
		[ShipCity],
		[ShipRegion],
		[ShipPostalCode],
		[ShipCountry]
	FROM [Orders]
	WHERE
		([OrderID] = @OrderID)

	SET @Err = @@Error

	RETURN @Err
END
GO


-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: daab_GetOrders Succeeded'
ELSE PRINT 'Procedure Creation: daab_GetOrders Error on Creation'
GO

IF EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID('daab_GetAllOrders') AND sysstat & 0xf = 4)
    DROP PROCEDURE [daab_GetAllOrders];
GO

CREATE PROCEDURE [daab_GetAllOrders]
AS
BEGIN

	SET NOCOUNT ON
	DECLARE @Err int

	SELECT
		[OrderID],
		[CustomerID],
		[EmployeeID],
		[OrderDate],
		[RequiredDate],
		[ShippedDate],
		[ShipVia],
		[Freight],
		[ShipName],
		[ShipAddress],
		[ShipCity],
		[ShipRegion],
		[ShipPostalCode],
		[ShipCountry]
	FROM [Orders]

	SET @Err = @@Error

	RETURN @Err
END
GO


-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: daab_GetAllOrders Succeeded'
ELSE PRINT 'Procedure Creation: daab_GetAllOrders Error on Creation'
GO

IF EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID('daab_UpdateOrders') AND sysstat & 0xf = 4)
    DROP PROCEDURE [daab_UpdateOrders];
GO

CREATE PROCEDURE [daab_UpdateOrders]
(
	@OrderID int,
	@CustomerID nchar(5) = NULL,
	@EmployeeID int = NULL,
	@OrderDate datetime = NULL,
	@RequiredDate datetime = NULL,
	@ShippedDate datetime = NULL,
	@ShipVia int = NULL,
	@Freight money = NULL,
	@ShipName nvarchar(40) = NULL,
	@ShipAddress nvarchar(60) = NULL,
	@ShipCity nvarchar(15) = NULL,
	@ShipRegion nvarchar(15) = NULL,
	@ShipPostalCode nvarchar(10) = NULL,
	@ShipCountry nvarchar(15) = NULL
)
AS
BEGIN

	SET NOCOUNT ON
	DECLARE @Err int

	UPDATE [Orders]
	SET
		[CustomerID] = @CustomerID,
		[EmployeeID] = @EmployeeID,
		[OrderDate] = @OrderDate,
		[RequiredDate] = @RequiredDate,
		[ShippedDate] = @ShippedDate,
		[ShipVia] = @ShipVia,
		[Freight] = @Freight,
		[ShipName] = @ShipName,
		[ShipAddress] = @ShipAddress,
		[ShipCity] = @ShipCity,
		[ShipRegion] = @ShipRegion,
		[ShipPostalCode] = @ShipPostalCode,
		[ShipCountry] = @ShipCountry
	WHERE
		[OrderID] = @OrderID


	SET @Err = @@Error

	RETURN @Err
END
GO


-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: daab_UpdateOrders Succeeded'
ELSE PRINT 'Procedure Creation: daab_UpdateOrders Error on Creation'
GO




IF EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID('daab_AddOrders') AND sysstat & 0xf = 4)
    DROP PROCEDURE [daab_AddOrders];
GO

CREATE PROCEDURE [daab_AddOrders]
(
	@OrderID int = NULL OUTPUT,
	@CustomerID nchar(5) = NULL,
	@EmployeeID int = NULL,
	@OrderDate datetime = NULL,
	@RequiredDate datetime = NULL,
	@ShippedDate datetime = NULL,
	@ShipVia int = NULL,
	@Freight money = NULL,
	@ShipName nvarchar(40) = NULL,
	@ShipAddress nvarchar(60) = NULL,
	@ShipCity nvarchar(15) = NULL,
	@ShipRegion nvarchar(15) = NULL,
	@ShipPostalCode nvarchar(10) = NULL,
	@ShipCountry nvarchar(15) = NULL
)
AS
BEGIN

	SET NOCOUNT ON
	DECLARE @Err int

	INSERT
	INTO [Orders]
	(
		[CustomerID],
		[EmployeeID],
		[OrderDate],
		[RequiredDate],
		[ShippedDate],
		[ShipVia],
		[Freight],
		[ShipName],
		[ShipAddress],
		[ShipCity],
		[ShipRegion],
		[ShipPostalCode],
		[ShipCountry]
	)
	VALUES
	(
		@CustomerID,
		@EmployeeID,
		@OrderDate,
		@RequiredDate,
		@ShippedDate,
		@ShipVia,
		@Freight,
		@ShipName,
		@ShipAddress,
		@ShipCity,
		@ShipRegion,
		@ShipPostalCode,
		@ShipCountry
	)

	SET @Err = @@Error
	SELECT @OrderID = SCOPE_IDENTITY()

	RETURN @Err
END
GO


-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: daab_AddOrders Succeeded'
ELSE PRINT 'Procedure Creation: daab_AddOrders Error on Creation'
GO

IF EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID('daab_DeleteOrders') AND sysstat & 0xf = 4)
    DROP PROCEDURE [daab_DeleteOrders];
GO

CREATE PROCEDURE [daab_DeleteOrders]
(
	@OrderID int
)
AS
BEGIN

	SET NOCOUNT ON
	DECLARE @Err int

	DELETE
	FROM [Orders]
	WHERE
		[OrderID] = @OrderID
	SET @Err = @@Error

	RETURN @Err
END
GO


-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: daab_DeleteOrders Succeeded'
ELSE PRINT 'Procedure Creation: daab_DeleteOrders Error on Creation'
GO

IF EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID('daab_GetProducts') AND sysstat & 0xf = 4)
    DROP PROCEDURE [daab_GetProducts];
GO

CREATE PROCEDURE [daab_GetProducts]
(
	@ProductID int
)
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @Err int

	SELECT
		[ProductID],
		[ProductName],
		[SupplierID],
		[CategoryID],
		[QuantityPerUnit],
		[UnitPrice],
		[UnitsInStock],
		[UnitsOnOrder],
		[ReorderLevel],
		[Discontinued]
	FROM [Products]
	WHERE
		([ProductID] = @ProductID)

	SET @Err = @@Error

	RETURN @Err
END
GO


-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: daab_GetProducts Succeeded'
ELSE PRINT 'Procedure Creation: daab_GetProducts Error on Creation'
GO

IF EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID('daab_GetAllProducts') AND sysstat & 0xf = 4)
    DROP PROCEDURE [daab_GetAllProducts];
GO

CREATE PROCEDURE [daab_GetAllProducts]
AS
BEGIN

	SET NOCOUNT ON
	DECLARE @Err int

	SELECT
		[ProductID],
		[ProductName],
		[SupplierID],
		[CategoryID],
		[QuantityPerUnit],
		[UnitPrice],
		[UnitsInStock],
		[UnitsOnOrder],
		[ReorderLevel],
		[Discontinued]
	FROM [Products]

	SET @Err = @@Error

	RETURN @Err
END
GO


-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: daab_GetAllProducts Succeeded'
ELSE PRINT 'Procedure Creation: daab_GetAllProducts Error on Creation'
GO

IF EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID('daab_UpdateProducts') AND sysstat & 0xf = 4)
    DROP PROCEDURE [daab_UpdateProducts];
GO

CREATE PROCEDURE [daab_UpdateProducts]
(
	@ProductID int,
	@ProductName nvarchar(40),
	@SupplierID int = NULL,
	@CategoryID int = NULL,
	@QuantityPerUnit nvarchar(20) = NULL,
	@UnitPrice money = NULL,
	@UnitsInStock smallint = NULL,
	@UnitsOnOrder smallint = NULL,
	@ReorderLevel smallint = NULL,
	@Discontinued bit
)
AS
BEGIN

	SET NOCOUNT ON
	DECLARE @Err int

	UPDATE [Products]
	SET
		[ProductName] = @ProductName,
		[SupplierID] = @SupplierID,
		[CategoryID] = @CategoryID,
		[QuantityPerUnit] = @QuantityPerUnit,
		[UnitPrice] = @UnitPrice,
		[UnitsInStock] = @UnitsInStock,
		[UnitsOnOrder] = @UnitsOnOrder,
		[ReorderLevel] = @ReorderLevel,
		[Discontinued] = @Discontinued
	WHERE
		[ProductID] = @ProductID


	SET @Err = @@Error

	RETURN @Err
END
GO


-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: daab_UpdateProducts Succeeded'
ELSE PRINT 'Procedure Creation: daab_UpdateProducts Error on Creation'
GO




IF EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID('daab_AddProducts') AND sysstat & 0xf = 4)
    DROP PROCEDURE [daab_AddProducts];
GO

CREATE PROCEDURE [daab_AddProducts]
(
	@ProductID int = NULL OUTPUT,
	@ProductName nvarchar(40),
	@SupplierID int = NULL,
	@CategoryID int = NULL,
	@QuantityPerUnit nvarchar(20) = NULL,
	@UnitPrice money = NULL,
	@UnitsInStock smallint = NULL,
	@UnitsOnOrder smallint = NULL,
	@ReorderLevel smallint = NULL,
	@Discontinued bit
)
AS
BEGIN

	SET NOCOUNT ON
	DECLARE @Err int

	INSERT
	INTO [Products]
	(
		[ProductName],
		[SupplierID],
		[CategoryID],
		[QuantityPerUnit],
		[UnitPrice],
		[UnitsInStock],
		[UnitsOnOrder],
		[ReorderLevel],
		[Discontinued]
	)
	VALUES
	(
		@ProductName,
		@SupplierID,
		@CategoryID,
		@QuantityPerUnit,
		@UnitPrice,
		@UnitsInStock,
		@UnitsOnOrder,
		@ReorderLevel,
		@Discontinued
	)

	SET @Err = @@Error
	SELECT @ProductID = SCOPE_IDENTITY()

	RETURN @Err
END
GO


-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: daab_AddProducts Succeeded'
ELSE PRINT 'Procedure Creation: daab_AddProducts Error on Creation'
GO

IF EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID('daab_DeleteProducts') AND sysstat & 0xf = 4)
    DROP PROCEDURE [daab_DeleteProducts];
GO

CREATE PROCEDURE [daab_DeleteProducts]
(
	@ProductID int
)
AS
BEGIN

	SET NOCOUNT ON
	DECLARE @Err int

	DELETE
	FROM [Products]
	WHERE
		[ProductID] = @ProductID
	SET @Err = @@Error

	RETURN @Err
END
GO


-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: daab_DeleteProducts Succeeded'
ELSE PRINT 'Procedure Creation: daab_DeleteProducts Error on Creation'
GO

IF EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID('daab_GetRegion') AND sysstat & 0xf = 4)
    DROP PROCEDURE [daab_GetRegion];
GO

CREATE PROCEDURE [daab_GetRegion]
(
	@RegionID int
)
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @Err int

	SELECT
		[RegionID],
		[RegionDescription]
	FROM [Region]
	WHERE
		([RegionID] = @RegionID)

	SET @Err = @@Error

	RETURN @Err
END
GO


-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: daab_GetRegion Succeeded'
ELSE PRINT 'Procedure Creation: daab_GetRegion Error on Creation'
GO

IF EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID('daab_GetAllRegion') AND sysstat & 0xf = 4)
    DROP PROCEDURE [daab_GetAllRegion];
GO

CREATE PROCEDURE [daab_GetAllRegion]
AS
BEGIN

	SET NOCOUNT ON
	DECLARE @Err int

	SELECT
		[RegionID],
		[RegionDescription]
	FROM [Region]

	SET @Err = @@Error

	RETURN @Err
END
GO


-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: daab_GetAllRegion Succeeded'
ELSE PRINT 'Procedure Creation: daab_GetAllRegion Error on Creation'
GO

IF EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID('daab_UpdateRegion') AND sysstat & 0xf = 4)
    DROP PROCEDURE [daab_UpdateRegion];
GO

CREATE PROCEDURE [daab_UpdateRegion]
(
	@RegionID int,
	@RegionDescription nchar(50)
)
AS
BEGIN

	SET NOCOUNT ON
	DECLARE @Err int

	UPDATE [Region]
	SET
		[RegionDescription] = @RegionDescription
	WHERE
		[RegionID] = @RegionID


	SET @Err = @@Error

	RETURN @Err
END
GO


-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: daab_UpdateRegion Succeeded'
ELSE PRINT 'Procedure Creation: daab_UpdateRegion Error on Creation'
GO




IF EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID('daab_AddRegion') AND sysstat & 0xf = 4)
    DROP PROCEDURE [daab_AddRegion];
GO

CREATE PROCEDURE [daab_AddRegion]
(
	@RegionID int,
	@RegionDescription nchar(50)
)
AS
BEGIN

	SET NOCOUNT ON
	DECLARE @Err int

	INSERT
	INTO [Region]