easyobjectsquickstarts.sql

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

	(
		[RegionID],
		[RegionDescription]
	)
	VALUES
	(
		@RegionID,
		@RegionDescription
	)

	SET @Err = @@Error

	RETURN @Err
END
GO


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

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

CREATE PROCEDURE [daab_DeleteRegion]
(
	@RegionID int
)
AS
BEGIN

	SET NOCOUNT ON
	DECLARE @Err int

	DELETE
	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_DeleteRegion Succeeded'
ELSE PRINT 'Procedure Creation: daab_DeleteRegion Error on Creation'
GO

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

CREATE PROCEDURE [daab_GetShippers]
(
	@ShipperID int
)
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @Err int

	SELECT
		[ShipperID],
		[CompanyName],
		[Phone]
	FROM [Shippers]
	WHERE
		([ShipperID] = @ShipperID)

	SET @Err = @@Error

	RETURN @Err
END
GO


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

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

CREATE PROCEDURE [daab_GetAllShippers]
AS
BEGIN

	SET NOCOUNT ON
	DECLARE @Err int

	SELECT
		[ShipperID],
		[CompanyName],
		[Phone]
	FROM [Shippers]

	SET @Err = @@Error

	RETURN @Err
END
GO


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

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

CREATE PROCEDURE [daab_UpdateShippers]
(
	@ShipperID int,
	@CompanyName nvarchar(40),
	@Phone nvarchar(24) = NULL
)
AS
BEGIN

	SET NOCOUNT ON
	DECLARE @Err int

	UPDATE [Shippers]
	SET
		[CompanyName] = @CompanyName,
		[Phone] = @Phone
	WHERE
		[ShipperID] = @ShipperID


	SET @Err = @@Error

	RETURN @Err
END
GO


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




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

CREATE PROCEDURE [daab_AddShippers]
(
	@ShipperID int = NULL OUTPUT,
	@CompanyName nvarchar(40),
	@Phone nvarchar(24) = NULL
)
AS
BEGIN

	SET NOCOUNT ON
	DECLARE @Err int

	INSERT
	INTO [Shippers]
	(
		[CompanyName],
		[Phone]
	)
	VALUES
	(
		@CompanyName,
		@Phone
	)

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

	RETURN @Err
END
GO


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

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

CREATE PROCEDURE [daab_DeleteShippers]
(
	@ShipperID int
)
AS
BEGIN

	SET NOCOUNT ON
	DECLARE @Err int

	DELETE
	FROM [Shippers]
	WHERE
		[ShipperID] = @ShipperID
	SET @Err = @@Error

	RETURN @Err
END
GO


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

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

CREATE PROCEDURE [daab_GetSuppliers]
(
	@SupplierID int
)
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @Err int

	SELECT
		[SupplierID],
		[CompanyName],
		[ContactName],
		[ContactTitle],
		[Address],
		[City],
		[Region],
		[PostalCode],
		[Country],
		[Phone],
		[Fax],
		[HomePage]
	FROM [Suppliers]
	WHERE
		([SupplierID] = @SupplierID)

	SET @Err = @@Error

	RETURN @Err
END
GO


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

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

CREATE PROCEDURE [daab_GetAllSuppliers]
AS
BEGIN

	SET NOCOUNT ON
	DECLARE @Err int

	SELECT
		[SupplierID],
		[CompanyName],
		[ContactName],
		[ContactTitle],
		[Address],
		[City],
		[Region],
		[PostalCode],
		[Country],
		[Phone],
		[Fax],
		[HomePage]
	FROM [Suppliers]

	SET @Err = @@Error

	RETURN @Err
END
GO


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

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

CREATE PROCEDURE [daab_UpdateSuppliers]
(
	@SupplierID int,
	@CompanyName nvarchar(40),
	@ContactName nvarchar(30) = NULL,
	@ContactTitle nvarchar(30) = NULL,
	@Address nvarchar(60) = NULL,
	@City nvarchar(15) = NULL,
	@Region nvarchar(15) = NULL,
	@PostalCode nvarchar(10) = NULL,
	@Country nvarchar(15) = NULL,
	@Phone nvarchar(24) = NULL,
	@Fax nvarchar(24) = NULL,
	@HomePage ntext = NULL
)
AS
BEGIN

	SET NOCOUNT ON
	DECLARE @Err int

	UPDATE [Suppliers]
	SET
		[CompanyName] = @CompanyName,
		[ContactName] = @ContactName,
		[ContactTitle] = @ContactTitle,
		[Address] = @Address,
		[City] = @City,
		[Region] = @Region,
		[PostalCode] = @PostalCode,
		[Country] = @Country,
		[Phone] = @Phone,
		[Fax] = @Fax,
		[HomePage] = @HomePage
	WHERE
		[SupplierID] = @SupplierID


	SET @Err = @@Error

	RETURN @Err
END
GO


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




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

CREATE PROCEDURE [daab_AddSuppliers]
(
	@SupplierID int = NULL OUTPUT,
	@CompanyName nvarchar(40),
	@ContactName nvarchar(30) = NULL,
	@ContactTitle nvarchar(30) = NULL,
	@Address nvarchar(60) = NULL,
	@City nvarchar(15) = NULL,
	@Region nvarchar(15) = NULL,
	@PostalCode nvarchar(10) = NULL,
	@Country nvarchar(15) = NULL,
	@Phone nvarchar(24) = NULL,
	@Fax nvarchar(24) = NULL,
	@HomePage ntext = NULL
)
AS
BEGIN

	SET NOCOUNT ON
	DECLARE @Err int

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

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

	RETURN @Err
END
GO


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

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

CREATE PROCEDURE [daab_DeleteSuppliers]
(
	@SupplierID int
)
AS
BEGIN

	SET NOCOUNT ON
	DECLARE @Err int

	DELETE
	FROM [Suppliers]
	WHERE
		[SupplierID] = @SupplierID
	SET @Err = @@Error

	RETURN @Err
END
GO


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

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

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

	SELECT
		[TerritoryID],
		[TerritoryDescription],
		[RegionID]
	FROM [Territories]
	WHERE
		([TerritoryID] = @TerritoryID)

	SET @Err = @@Error

	RETURN @Err
END
GO


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

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

CREATE PROCEDURE [daab_GetAllTerritories]
AS
BEGIN

	SET NOCOUNT ON
	DECLARE @Err int

	SELECT
		[TerritoryID],
		[TerritoryDescription],
		[RegionID]
	FROM [Territories]

	SET @Err = @@Error

	RETURN @Err
END
GO


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

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

CREATE PROCEDURE [daab_UpdateTerritories]
(
	@TerritoryID nvarchar(20),
	@TerritoryDescription nchar(50),
	@RegionID int
)
AS
BEGIN

	SET NOCOUNT ON
	DECLARE @Err int

	UPDATE [Territories]
	SET
		[TerritoryDescription] = @TerritoryDescription,
		[RegionID] = @RegionID
	WHERE
		[TerritoryID] = @TerritoryID


	SET @Err = @@Error

	RETURN @Err
END
GO


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




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

CREATE PROCEDURE [daab_AddTerritories]
(
	@TerritoryID nvarchar(20),
	@TerritoryDescription nchar(50),
	@RegionID int
)
AS
BEGIN

	SET NOCOUNT ON
	DECLARE @Err int

	INSERT
	INTO [Territories]
	(
		[TerritoryID],
		[TerritoryDescription],
		[RegionID]
	)
	VALUES
	(
		@TerritoryID,
		@TerritoryDescription,
		@RegionID
	)

	SET @Err = @@Error

	RETURN @Err
END
GO


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

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

CREATE PROCEDURE [daab_DeleteTerritories]
(
	@TerritoryID nvarchar(20)
)
AS
BEGIN

	SET NOCOUNT ON
	DECLARE @Err int

	DELETE
	FROM [Territories]
	WHERE
		[TerritoryID] = @TerritoryID
	SET @Err = @@Error

	RETURN @Err
END
GO


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