easyobjectsquickstarts.sql

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

)
AS
BEGIN

	SET NOCOUNT ON
	DECLARE @Err int

	INSERT
	INTO [Customers]
	(
		[CustomerID],
		[CompanyName],
		[ContactName],
		[ContactTitle],
		[Address],
		[City],
		[Region],
		[PostalCode],
		[Country],
		[Phone],
		[Fax]
	)
	VALUES
	(
		@CustomerID,
		@CompanyName,
		@ContactName,
		@ContactTitle,
		@Address,
		@City,
		@Region,
		@PostalCode,
		@Country,
		@Phone,
		@Fax
	)

	SET @Err = @@Error

	RETURN @Err
END
GO


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

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

CREATE PROCEDURE [daab_DeleteCustomers]
(
	@CustomerID nchar(5)
)
AS
BEGIN

	SET NOCOUNT ON
	DECLARE @Err int

	DELETE
	FROM [Customers]
	WHERE
		[CustomerID] = @CustomerID
	SET @Err = @@Error

	RETURN @Err
END
GO


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

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

CREATE PROCEDURE [daab_GetEmployees]
(
	@EmployeeID int
)
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @Err int

	SELECT
		[EmployeeID],
		[LastName],
		[FirstName],
		[Title],
		[TitleOfCourtesy],
		[BirthDate],
		[HireDate],
		[Address],
		[City],
		[Region],
		[PostalCode],
		[Country],
		[HomePhone],
		[Extension],
		[Photo],
		[Notes],
		[ReportsTo],
		[PhotoPath]
	FROM [Employees]
	WHERE
		([EmployeeID] = @EmployeeID)

	SET @Err = @@Error

	RETURN @Err
END
GO


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

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

CREATE PROCEDURE [daab_GetAllEmployees]
AS
BEGIN

	SET NOCOUNT ON
	DECLARE @Err int

	SELECT
		[EmployeeID],
		[LastName],
		[FirstName],
		[Title],
		[TitleOfCourtesy],
		[BirthDate],
		[HireDate],
		[Address],
		[City],
		[Region],
		[PostalCode],
		[Country],
		[HomePhone],
		[Extension],
		[Photo],
		[Notes],
		[ReportsTo],
		[PhotoPath]
	FROM [Employees]

	SET @Err = @@Error

	RETURN @Err
END
GO


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

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

CREATE PROCEDURE [daab_UpdateEmployees]
(
	@EmployeeID int,
	@LastName nvarchar(20),
	@FirstName nvarchar(10),
	@Title nvarchar(30) = NULL,
	@TitleOfCourtesy nvarchar(25) = NULL,
	@BirthDate datetime = NULL,
	@HireDate datetime = NULL,
	@Address nvarchar(60) = NULL,
	@City nvarchar(15) = NULL,
	@Region nvarchar(15) = NULL,
	@PostalCode nvarchar(10) = NULL,
	@Country nvarchar(15) = NULL,
	@HomePhone nvarchar(24) = NULL,
	@Extension nvarchar(4) = NULL,
	@Photo image = NULL,
	@Notes ntext = NULL,
	@ReportsTo int = NULL,
	@PhotoPath nvarchar(255) = NULL
)
AS
BEGIN

	SET NOCOUNT ON
	DECLARE @Err int

	UPDATE [Employees]
	SET
		[LastName] = @LastName,
		[FirstName] = @FirstName,
		[Title] = @Title,
		[TitleOfCourtesy] = @TitleOfCourtesy,
		[BirthDate] = @BirthDate,
		[HireDate] = @HireDate,
		[Address] = @Address,
		[City] = @City,
		[Region] = @Region,
		[PostalCode] = @PostalCode,
		[Country] = @Country,
		[HomePhone] = @HomePhone,
		[Extension] = @Extension,
		[Photo] = @Photo,
		[Notes] = @Notes,
		[ReportsTo] = @ReportsTo,
		[PhotoPath] = @PhotoPath
	WHERE
		[EmployeeID] = @EmployeeID


	SET @Err = @@Error

	RETURN @Err
END
GO


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




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

CREATE PROCEDURE [daab_AddEmployees]
(
	@EmployeeID int = NULL OUTPUT,
	@LastName nvarchar(20),
	@FirstName nvarchar(10),
	@Title nvarchar(30) = NULL,
	@TitleOfCourtesy nvarchar(25) = NULL,
	@BirthDate datetime = NULL,
	@HireDate datetime = NULL,
	@Address nvarchar(60) = NULL,
	@City nvarchar(15) = NULL,
	@Region nvarchar(15) = NULL,
	@PostalCode nvarchar(10) = NULL,
	@Country nvarchar(15) = NULL,
	@HomePhone nvarchar(24) = NULL,
	@Extension nvarchar(4) = NULL,
	@Photo image = NULL,
	@Notes ntext = NULL,
	@ReportsTo int = NULL,
	@PhotoPath nvarchar(255) = NULL
)
AS
BEGIN

	SET NOCOUNT ON
	DECLARE @Err int

	INSERT
	INTO [Employees]
	(
		[LastName],
		[FirstName],
		[Title],
		[TitleOfCourtesy],
		[BirthDate],
		[HireDate],
		[Address],
		[City],
		[Region],
		[PostalCode],
		[Country],
		[HomePhone],
		[Extension],
		[Photo],
		[Notes],
		[ReportsTo],
		[PhotoPath]
	)
	VALUES
	(
		@LastName,
		@FirstName,
		@Title,
		@TitleOfCourtesy,
		@BirthDate,
		@HireDate,
		@Address,
		@City,
		@Region,
		@PostalCode,
		@Country,
		@HomePhone,
		@Extension,
		@Photo,
		@Notes,
		@ReportsTo,
		@PhotoPath
	)

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

	RETURN @Err
END
GO


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

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

CREATE PROCEDURE [daab_DeleteEmployees]
(
	@EmployeeID int
)
AS
BEGIN

	SET NOCOUNT ON
	DECLARE @Err int

	DELETE
	FROM [Employees]
	WHERE
		[EmployeeID] = @EmployeeID
	SET @Err = @@Error

	RETURN @Err
END
GO


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

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

CREATE PROCEDURE [daab_GetEmployeeTerritories]
(
	@EmployeeID int,
	@TerritoryID nvarchar(20)
)
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @Err int

	SELECT
		[EmployeeID],
		[TerritoryID]
	FROM [EmployeeTerritories]
	WHERE
		([EmployeeID] = @EmployeeID) AND
		([TerritoryID] = @TerritoryID)

	SET @Err = @@Error

	RETURN @Err
END
GO


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

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

CREATE PROCEDURE [daab_GetAllEmployeeTerritories]
AS
BEGIN

	SET NOCOUNT ON
	DECLARE @Err int

	SELECT
		[EmployeeID],
		[TerritoryID]
	FROM [EmployeeTerritories]

	SET @Err = @@Error

	RETURN @Err
END
GO


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

-------------------------------------------
-- NO UPDATE Stored Procedure Generated    
-- All Columns are part of the Primary key 
-------------------------------------------


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

CREATE PROCEDURE [daab_AddEmployeeTerritories]
(
	@EmployeeID int,
	@TerritoryID nvarchar(20)
)
AS
BEGIN

	SET NOCOUNT ON
	DECLARE @Err int

	INSERT
	INTO [EmployeeTerritories]
	(
		[EmployeeID],
		[TerritoryID]
	)
	VALUES
	(
		@EmployeeID,
		@TerritoryID
	)

	SET @Err = @@Error

	RETURN @Err
END
GO


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

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

CREATE PROCEDURE [daab_DeleteEmployeeTerritories]
(
	@EmployeeID int,
	@TerritoryID nvarchar(20)
)
AS
BEGIN

	SET NOCOUNT ON
	DECLARE @Err int

	DELETE
	FROM [EmployeeTerritories]
	WHERE
		[EmployeeID] = @EmployeeID AND
		[TerritoryID] = @TerritoryID
	SET @Err = @@Error

	RETURN @Err
END
GO


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

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

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

	SELECT
		[OrderID],
		[ProductID],
		[UnitPrice],
		[Quantity],
		[Discount]
	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_GetOrderDetails Succeeded'
ELSE PRINT 'Procedure Creation: daab_GetOrderDetails Error on Creation'
GO

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

CREATE PROCEDURE [daab_GetAllOrderDetails]
AS
BEGIN

	SET NOCOUNT ON
	DECLARE @Err int

	SELECT
		[OrderID],
		[ProductID],
		[UnitPrice],
		[Quantity],
		[Discount]
	FROM [Order Details]

	SET @Err = @@Error

	RETURN @Err
END
GO


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

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

CREATE PROCEDURE [daab_UpdateOrderDetails]
(
	@OrderID int,
	@ProductID int,
	@UnitPrice money,
	@Quantity smallint,
	@Discount real
)
AS
BEGIN

	SET NOCOUNT ON
	DECLARE @Err int

	UPDATE [Order Details]
	SET
		[UnitPrice] = @UnitPrice,
		[Quantity] = @Quantity,
		[Discount] = @Discount
	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_UpdateOrderDetails Succeeded'
ELSE PRINT 'Procedure Creation: daab_UpdateOrderDetails Error on Creation'
GO




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

CREATE PROCEDURE [daab_AddOrderDetails]
(
	@OrderID int,
	@ProductID int,
	@UnitPrice money,
	@Quantity smallint,
	@Discount real
)
AS
BEGIN

	SET NOCOUNT ON
	DECLARE @Err int

	INSERT
	INTO [Order Details]
	(
		[OrderID],
		[ProductID],
		[UnitPrice],
		[Quantity],
		[Discount]
	)
	VALUES
	(