3.6 First Stored Procedure : Creating the Stored Procedure

The most basic syntax of a stored procedure is:

create procedure <procedure name>

as

<SQL statements>

A very simple procedure to select all records from the product table is show in the next listing - don't run this example on your development database.

create procedure get_product

as

select * from product

This is a perfectly valid stored procedure but it doesn't meet our date requirements. Instead, we will simply take the batch of SQL that we have already tested and encapsulate it into a stored procedure. Copy, paste and run this SQL on your development database (if you did run the previous code against our advice - issue the statement drop procedure get_product first!)

create procedure get_product

as

 

select product_id, product_name, start_date
from product
where start_date <= getdate()

If there is no output from the SQL statement - that's a good sign, the stored procedure has been created.

If you have output, it could look like below. You've misspelled the column name and the stored procedure hasn't been created. Try again!

Invalid column name 'proodct_id'

Now we've created the stored procedure, its time to test it.