Stored Procedure to Insert and Update

INSERT

/*

 * Create a stored procedure to insert a product.

 */

CREATE PROCEDURE dbo.ttInsertProduct

(

@ProductName nvarchar(40),

@SupplierID int,

@CategoryID int,

@QuantityPerUnit nvarchar(20),

@UnitPrice money,

@UnitsInStock smallint, 

@UnitsOnOrder smallint, 

@ReorderLevel smallint,

@Discontinued bit

)

AS

INSERT Products 

(

ProductName,

SupplierID,

CategoryID,

QuantityPerUnit,

UnitPrice,

UnitsInStock, 

UnitsOnOrder, 

ReorderLevel,

Discontinued 

)

VALUES

(

@ProductName,

@SupplierID,

@CategoryID,

@QuantityPerUnit,

@UnitPrice,

@UnitsInStock, 

@UnitsOnOrder, 

@ReorderLevel,

@Discontinued

)

RETURN

GO

UPDATE

/*

 * Create a stored procedure to update a product.

 */

CREATE PROCEDURE dbo.ttUpdateProduct

(

@ProductID int,

@ProductName nvarchar(40),

@SupplierID int,

@CategoryID int,

@QuantityPerUnit nvarchar(20),

@UnitPrice money,

@UnitsInStock smallint, 

@UnitsOnOrder smallint, 

@ReorderLevel smallint,

@Discontinued bit

)

AS

UPDATE Products SET

ProductName = @ProductName,

SupplierID = @SupplierID,

CategoryID = @CategoryID,

QuantityPerUnit = @QuantityPerUnit,

UnitPrice = @UnitPrice,

UnitsInStock = @UnitsInStock, 

UnitsOnOrder = @UnitsOnOrder, 

ReorderLevel = @ReorderLevel,

Discontinued = @Discontinued 

WHERE 

ProductID=@ProductID

RETURN

GO