3.5 First Stored Procedure : Coding the Transact-SQL
We are now going to create a stored procedure that meets the requirements of the first Use Case.
Use Case: The customer views a list of products that are available to trade.
We will create a stored procedure that returns a list of all products in the product table. We have one set of criteria for filtering the data: when we run the stored procedure at a particular time, the product start date must be equal or before that time.
Always code and test SQL before encapsulating it in a stored procedure - its much easier to debug that way. After a few iterations of testing, we decide on this query:
select product_id, product_name, start_date from product where start_date <= getdate()
This is the output that you'll see when you run this batch of Transact-SQL.
product_id product_name start_date 1 Pink Widget 2007 Jan 1 2007 3 Gray Widget 2005 Jan 1 2005