I have written a stored procedure that is using one temporary table.

When i run the statements sequentially in the query analyzer it takes about
2 minutes to process everything.

Once i put the statements into a stored procedure and execute the procedure,
it runs for about 40 minutes.

Any ideas on what could be causing this?

It would help to see the actual code.  Sounds like you are using a cursor.
But do you have SET NOCOUNT ON at the beginning of the stored procedure?

Show quoteHide quote

Allan (Al***@discussions.microsoft.com) writes:
There are plenty of options. The most common reason for this sort of
problems are:

1) When you ran it outside QA you had hard-coded values, when you ran the
   SP you used parameters, or even values from variables.

2) Your query involves an indexed view or an indexed computed column, and
   you created the stored procedure in Enterprise Manager, and did not
   change the settings for ANSI_NULLS and QUOTED_IDENTIFIER. They are
   OFF by default in EM, but must be ON for indexed viewes and indexed
   computed colunms to be in play. (These settings are saved with the
   procedure.)

You would need to include the procedure code, as well as the code you
run in QA to get more precise answers.

Does dynamic SQL allow table variables?
DBCC SHOWCONTIG question
Insert Trigger
Problem with Cursor and Union in select
Optimizing query with UDF and table vars and IN
Reducing 5 values to 1 value
Moving indexes from a filegroup to another
SELECT problem in stored procedure
sql server 2005 error handling
Where to put indexes