Friday, February 13, 2015

T-SQL: Stored Procedures (UPDATE), UPDATE An Existing Product In Northwind Part 4

Here is how you would create a stored procedure to update an a new record into the Products table in the Northwind database.

USE Northwind
GO
CREATE PROCEDURE dbo.updProduct(
@ProductID int,
@ProductName nvarchar(40),
@SupplierID int = null, --default is null
@CategoryID int = null,
@QuantityPerUnit nvarchar(20) = null,
@UnitPrice money = null,
@UnitsInStock smallint = null,
@UnitsOnOrder smallint = null,
@ReorderLevel smallint = null,
@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 Products.ProductID = @ProductID
GO

When you see a parameter with the = null, it means the field can have a null value. Since we need ProductID is needed to update a specific record we need it in the input parameter list. The data types must match the fields in the database.
Now lets find an existing record in the database, let's use the product with the ProductID of 1

Here is how you would execute the stored procedure
EXEC dbo.updProduct 
@ProductID = 1,
@ProductName ='Teh Tarik',
@SupplierID = DEFAULT,
@CategoryID = DEFAULT,
@QuantityPerUnit ='20 boxes x 12 oz.',
@UnitPrice = 12.99,
@UnitsInStock = 5,
@UnitsOnOrder = 6,
@ReorderLevel = DEFAULT,
@Discontinued = 0

When you see a parameter with = DEFAULT it means to assign the DEFAULT value to the field, if the execution is completed successfully you should see the message.

(1 row(s) affected)
Here is how the updated row looks like now.


Blogs In the T-SQL Series:

No comments:

Post a Comment