3.8 Examining Stored Procedures
Let's review the text of the stored procedure just created. To do this, we can use another stored procedure - one provided by SQL Server as part of the installation of an SQL Server. This system procedure accepts the name of a stored procedure as a parameter, and outputs the stored procedure text as results. Try it.
exec sp_helptext get_product
This is the output:
Text
---------------------------------------------------------------------------------------------------------------
create procedure get_product
as
select product_id, product_name, start_date from product where start_date <= getdate()
In truth, the output from sp_helptext isn't as reader-friendly as we've presented here. If you have a GUI-based SQL Editor which extracts and formats stored procedures nicely- use it! Use the GUI explorer to navigate to the stored procedure and "extract" or "edit" the procedure.