CREATE PROCEDURE p_customers_add

@name varchar(50),
@address varchar(50),
@city varchar(25),
@state varchar(25),
@zip varchar(10),
@active bit,
@datecreated datetime,
@ID int OUTPUT

AS

INSERT INTO Customers
(name,address,city,state,zip,active,datecreated)

VALUES(@name,@address,@city,@state,@zip,@active,@datecreated)

SET @ID = @@IDENTITY

GO


CREATE PROCEDURE p_customers_delete

@customerid int

AS

DELETE FROM Customers
WHERE CustomerID = @CustomerID

GO


CREATE PROCEDURE p_customers_update

@customerid int,
@name varchar(50),
@address varchar(50),
@city varchar(25),
@state varchar(25),
@zip varchar(10),
@active bit,
@datecreated datetime

AS

UPDATE Customers
SET
Name = @name,
Address = @address,
City = @city,
State = @state,
Zip = @zip,
Active = @active,
DateCreated = @datecreated

WHERE CustomerID = @customerid

GO


CREATE PROCEDURE p_customers_retrieve

@customerid int = NULL

AS

SELECT

c.customerid,
c.name,
c.address,
c.city,
c.state,
c.zip,
c.active,
c.datecreated

FROM Customers c
WHERE ( c.customerid = @customerid OR @customerid IS NULL)

GO