Tuesday, February 10, 2015

T-SQL: Stored Procedure (INSERT), INSERT A New Product In Northwind Part 1

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

USE Northwind
GO
CREATE PROCEDURE dbo.addProduct(
@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
INSERT INTO Products(ProductName,
SupplierID,
CategoryID,
QuantityPerUnit,
UnitPrice,
UnitsInStock,
UnitsOnOrder,
ReorderLevel,
Discontinued)
VALUES(@ProductName,
@SupplierID,
@CategoryID,
@QuantityPerUnit,
@UnitPrice,
@UnitsInStock,
@UnitsOnOrder,
@ReorderLevel,
@Discontinued)
GO

When you see a parameter with the = null, it means the field can have a null value. Since the ProductID is auto incremented you don't include it. The data types must match the fields in the database.
Here is how you would execute the stored procedure
EXEC dbo.addProduct @ProductName ='Teh',
@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)

Blogs In the T-SQL Series:

No comments:

Post a Comment