CREATE TABLE [dbo].[tbl_Customer](
[Cust_ID] [int] IDENTITY(1,1) NOT NULL,
[Cust_Name] [varchar](50) NOT NULL,
[Cust_DOB] [datetime] NOT NULL,
[Cust_Address] [varchar](100) NOT NULL,
[Date_Created] [datetime] NOT NULL CONSTRAINT [DF_tbl_Customer_Date_Created] DEFAULT (getdate()),
[Date_Modified] [datetime] NOT NULL CONSTRAINT [DF_tbl_Customer_Date_Modified] DEFAULT (getdate())
) ON [PRIMARY]
GO
ALTER TABLE [tbl_Customer]
ADD CONSTRAINT [PK_tbl_Customer] PRIMARY KEY
CLUSTERED ([Cust_ID])ON [PRIMARY]
GO
CREATE PROCEDURE [dbo].[Usp_GetCustomer]
(
@CustID Int
)
AS
/**************************************
* PROCEDURE: Usp_GetCustomer
* PURPOSE: Get a Customer Record
* AUTHOR: Vishwa Mohan
* Date Created 10/15/2006
* NOTES:
********************************
* MODIFICATION LOG
* DATE AUTHOR DESCRIPTION
*----------------------------------
*
********************************/
SELECT Cust_ID, Cust_Name, Cust_DOB,Cust_Address, Date_Created,Date_Modified
FROM tbl_Customer(NOLOCK) WHERE Cust_ID=@CustID
GO
CREATE PROCEDURE [dbo].[Usp_GetCustomers]
AS
/***********************************
* PROCEDURE: Usp_GetCustomers
* PURPOSE: Get All Customer Record
* AUTHOR: Vishwa Mohan
* Date Created 10/15/2006
* NOTES:
**************************************
* MODIFICATION LOG
* DATE AUTHOR DESCRIPTION
*---------------------------------------
*
***************************************/
SELECT Cust_ID, Cust_Name, Cust_DOB,Cust_Address,Date_Created,Date_Modified
FROM tbl_Customer(NOLOCK)
GO
CREATE PROCEDURE [dbo].[Usp_InsertCustomer]
(
@CustName VarChar(50),
@CustDOB DateTime,
@CustAddress VarChar(100)
)
AS
/*********************************
* PROCEDURE: Usp_InsertCustomer
* PURPOSE: Inserts a Customer Record
* AUTHOR: Vishwa Mohan
* Date Created 10/15/2006
* NOTES:
************************************
* MODIFICATION LOG
* DATE AUTHOR DESCRIPTION
*-----------------------------------
*
*****************************************/
INSERT tbl_Customer(Cust_Name,Cust_DOB,Cust_Address, Date_Created,Date_Modified )
VALUES (@CustName,@CustDOB, @CustAddress,GetDate(),GetDate())
If @@RowCount>0
RETURN SCOPE_IDENTITY()
Else
RETURN -1
GO
CREATE PROCEDURE [dbo].[Usp_UpdateCustomer]
(
@CustID Int,
@CustName VarChar(50),
@CustDOB DateTime,
@CustAddress VarChar(100)
)
AS
/***************************************
* PROCEDURE: Usp_UpdateCustomer
* PURPOSE: Updates a Customer Record
* AUTHOR: Vishwa Mohan
* Date Created 10/15/2006
* NOTES:
*******************************************
* MODIFICATION LOG
* DATE AUTHOR DESCRIPTION
*--------------------------------------------
*
********************************/
UPDATE tbl_Customer
SET Cust_Name = @CustName,
Cust_DOB = @CustDOB,
Cust_Address = @CustAddress,
Date_Created = GetDate(),
Date_Modified = GetDate()
WHERE Cust_ID = @CustID
If @@RowCount>0
RETURN @CustID
Else
RETURN -1
GO
CREATE PROCEDURE [dbo].[Usp_DeleteCustomer]
(
@CustID Int
)
AS
/**********************************
* PROCEDURE: Usp_DeleteCustomer
* PURPOSE: Deletes a Customer Record
* AUTHOR: Vishwa Mohan
* Date Created 10/15/2006
* NOTES:
*****************************************
* MODIFICATION LOG
* DATE AUTHOR DESCRIPTION
*-------------------------------------
*
*****************************************/
DELETE tbl_Customer
WHERE Cust_ID = @CustID
If @@RowCount>0
RETURN @CustID
Else
RETURN -1
GO