How can I display the execution plan for a stored procedure?

SET SHOWPLAN_ALL ON
GO

-- FMTONLY will not exec stored proc
SET FMTONLY ON
GO

exec yourproc
GO

SET FMTONLY OFF
GO

SET SHOWPLAN_ALL OFF
GO

When executing a stored procedure in SQL Management Studio 2008 you can click Query -> Include Actual Execution Plan from the menu...its also on the tool bar

After reading through the comments executing seems to be an issue and to solve this issue i would recommend wrapping the execution of the stored procedure in a transaction rolling it back at the end

Use

SET SHOWPLAN_ALL ON
Go
exec myStoredProc 234
GO
SET SHOWPLAN_ALL OFF
GO

See http://msdn.microsoft.com/en-us/library/aa259203.aspx As long as you aren't using tmp tables i think this will work

There are quite a few ways to get the actual execution plan of a stored procedure.

SELECT
qp.query_plan, 
SQLText.text
FROM sys.dm_exec_cached_plans AS CP
 CROSS APPLY sys.dm_exec_sql_text( plan_handle)AS SQLText
 CROSS APPLY sys.dm_exec_query_plan( plan_handle)AS QP
 WHERE objtype = 'Proc' and cp.cacheobjtype = 'Compiled Plan'

looking at the plans on a production server with the statistics of the data in the production server may show a different plan then of a dev box with a smaller dataset.

There is a lot more data to look at, like how often is a procedure executed according to query cache

SELECT
    qp.query_plan, 
    CP.usecounts as [Executed], 
    DB_name(QP.dbid) as [Database],
    OBJECT_NAME(QP.objectid) as [Procedure],
    SQLText.text as [TSQL],
    so.create_date as [Procedure Created],
    so.modify_date as [Procedure  Modified]
FROM sys.dm_exec_cached_plans AS CP
CROSS APPLY sys.dm_exec_sql_text( plan_handle)AS SQLText
CROSS APPLY sys.dm_exec_query_plan( plan_handle)AS QP
join sys.objects as so on so.[object_id]=QP.objectid
WHERE objtype = 'Proc' and cp.cacheobjtype = 'Compiled Plan'

The XML query plan (the first column in both queries), contains the XML of the execution plan allowing you, in SSMS to click on it and view the actual plans but also allows you to scan for things you do not like to have like index scan or "god forbid" table scans.

SELECT
    qp.query_plan, 
    CP.usecounts as [Executed], 
    DB_name(QP.dbid) as [Database],
    OBJECT_NAME(QP.objectid) as [Procedure],
    SQLText.text as [TSQL],
    so.create_date as [Procedure Created],
    so.modify_date as [Procedure  Modified]
FROM sys.dm_exec_cached_plans AS CP
CROSS APPLY sys.dm_exec_sql_text( plan_handle)AS SQLText
CROSS APPLY sys.dm_exec_query_plan( plan_handle)AS QP
join sys.objects as so on so.[object_id]=QP.objectid
WHERE objtype = 'Proc' and cp.cacheobjtype = 'Compiled Plan'
and cast(qp.query_plan as nvarchar(max)) like '%loop%'

I sample this using a really bad way by casting the XML to string and then doing a wildcard search however XML queries are not things most do every day and string wildcards are easy for everyone.

Running the stored procedure in management studio (or query analyser) with show actual execution plan (from the query menu) enabled will show you the plan for the stored procedure after you have run it. If you cant run it there is show estimated execution plan (though in my experience that is often less accurate.)

You can also use Profiler to see the execution plan. You'll want to include the Performance : Show Plan Statistics Profile option and be sure to inlcude Binary Data in your columns.

You can then run any query or procedure and see the execution plan.

Edit

If you can't use profiler, and you don't want to open another window I suggest that you include a comment block at the begining of your stored procs. For example imagine the following:

/* 
     Description: This procedure does XYZ etc...
     DevelopedBy: Josh
     Created On:  4/27/09

     Execution: exec my_procName N'sampleparam', N'sampleparam'
*/

ALTER PROCEDURE  my_procName
   @p1 nvarchar(20),
   @p2 nvarchar(20)

AS

What this allows is that you can highlight just the execution purpose and turn on show execution plan. And run it.