Web Application Page Patterns

/****** Object:  StoredProcedure [proc_CustomerLoadByPrimaryKey]    Script Date: 1/16/2007 2:04:17 PM ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[proc_CustomerLoadByPrimaryKey]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [proc_CustomerLoadByPrimaryKey];
GO

CREATE PROCEDURE [proc_CustomerLoadByPrimaryKey]
(
	@CustomerID int
)
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @Err int

	SELECT
		[CustomerID],
		[Name],
		[Address]
	FROM [Customer]
	WHERE
		([CustomerID] = @CustomerID)

	SET @Err = @@Error

	RETURN @Err
END
GO


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

/****** Object:  StoredProcedure [proc_CustomerLoadAll]    Script Date: 1/16/2007 2:04:17 PM ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[proc_CustomerLoadAll]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [proc_CustomerLoadAll];
GO

CREATE PROCEDURE [proc_CustomerLoadAll]
AS
BEGIN

	SET NOCOUNT ON
	DECLARE @Err int

	SELECT
		[CustomerID],
		[Name],
		[Address]
	FROM [Customer]

	SET @Err = @@Error

	RETURN @Err
END
GO


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

/****** Object:  StoredProcedure [proc_CustomerUpdate]    Script Date: 1/16/2007 2:04:17 PM ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[proc_CustomerUpdate]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [proc_CustomerUpdate];
GO

CREATE PROCEDURE [proc_CustomerUpdate]
(
	@CustomerID int,
	@Name varchar(50) = NULL,
	@Address varchar(500) = NULL
)
AS
BEGIN

	SET NOCOUNT OFF
	DECLARE @Err int

	UPDATE [Customer]
	SET
		[Name] = @Name,
		[Address] = @Address
	WHERE
		[CustomerID] = @CustomerID


	SET @Err = @@Error


	RETURN @Err
END
GO


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




/****** Object:  StoredProcedure [proc_CustomerInsert]    Script Date: 1/16/2007 2:04:17 PM ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[proc_CustomerInsert]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [proc_CustomerInsert];
GO

CREATE PROCEDURE [proc_CustomerInsert]
(
	@CustomerID int = NULL output,
	@Name varchar(50) = NULL,
	@Address varchar(500) = NULL
)
AS
BEGIN

	SET NOCOUNT OFF
	DECLARE @Err int

	INSERT
	INTO [Customer]
	(
		[Name],
		[Address]
	)
	VALUES
	(
		@Name,
		@Address
	)

	SET @Err = @@Error

	SELECT @CustomerID = SCOPE_IDENTITY()

	RETURN @Err
END
GO


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

/****** Object:  StoredProcedure [proc_CustomerDelete]    Script Date: 1/16/2007 2:04:17 PM ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[proc_CustomerDelete]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [proc_CustomerDelete];
GO

CREATE PROCEDURE [proc_CustomerDelete]
(
	@CustomerID int
)
AS
BEGIN

	SET NOCOUNT OFF
	DECLARE @Err int

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

	RETURN @Err
END
GO


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