Strange stored procedure execution issue

Hi all,
I got to interesting issue and I have no clue what is/could be happening in the system.
We run SQL 2005 server and we do reporting system.
We have couple of procedures doing queries in a very similar way.

All was going fine, but two days ago one of the procedures started to behave very slow.

What I can see. The procedure runs for several minutes. It does a select query inside using CTE and parameters (datetime and nvarchar).

I thought it is something in the data causing bad execution, so I analyzed the query.

But, when I take the body of the sp to a new window in sql studio and execute it it runs fast as all the other procedures.

I tried to drop and create the sp again, I have created a copy of the sp, but it has no effect. Sp runs for ages the query for milliseconds.

There is quite lot of processes now on the database, many clients doing their work, but no one works with the sp.

Any ideas what to check?
Thanks in advance.
Tom

OK, let's change the question.
What is the main difference between executing stored procedure and executing statement, that is the body of the stored procedure?
I have significant performance difference. The statement itself is much faster than when it is wrapped with the stored procedure.

The statement:

Code:

DECLARE @StartDT DATETIME, @EndDT DATETIME, @Area NVARCHAR(50), @Language NVARCHAR(10);

SET @StartDT = '2013-01-16';
SET @EndDT = '2013-01-17';
SET @Area = 'Cold_Block';
SET @Language = 'EN';

WITH filterData (Recipe, RecipeType, UnitType, Unit, Circuit, [SERVER], [DATABASE]) AS
    (
        SELECT DISTINCT
            rer.Recipe,
            rer.RecipeType,
            tc.Translation,
            eqr.EQ_DestDescription,
            reorv.Circuit,
            @@SERVERNAME,
            DB_NAME()

        FROM dbo.tvf_rp3_REUnitProcedureByTime(@StartDT, @EndDT) reup
            JOIN (
                SELECT 'Line' as UT,* FROM dbo.tvf_rp3_EQUnit('CIPLCMN', NULL)
                UNION
                SELECT 'Tank' as UT,* FROM dbo.tvf_rp3_EQUnit('CIPTCMN', NULL)
            ) equ ON reup.EQ_UN_ID = equ.EQ_UN_ID
            JOIN dbo.tvf_rp3_RERecipe(@Area) rer ON reup.RE_RC_ID = rer.RE_RC_ID
            JOIN dbo.tvf_rp3_REOperationReportValues(NULL, NULL, NULL, @StartDT, @EndDT) reorv ON reup.RE_UP_ID = reorv.UPID AND reorv.Circuit IS NOT NULL
            JOIN dbo.tvf_rp3_TranslateCollection('UnitType', @Language) tc ON equ.UT = tc.Name
            JOIN dbo.EQ_Route eqr ON eqr.EQ_UN_ID_Manager = equ.EQ_UN_ID AND eqr.CipCircuit = reorv.Circuit
    )
    -- select all from CTE
    SELECT * FROM filterData
    -- add empty
    UNION
    SELECT NULL, NULL, NULL, NULL, NULL, @@SERVERNAME, DB_NAME()
    WHERE (SELECT COUNT(*) FROM filterData) = 0

The stored procedure:

Code:

CREATE PROCEDURE [dbo].[sp_ft3_CIPObject]
    -- parameters
    @StartDT    DATETIME,
    @EndDT        DATETIME,
    @Area        NVARCHAR(50),
    @Language    NVARCHAR(10)
AS
BEGIN
    -- define CTE
    WITH filterData (Recipe, RecipeType, UnitType, Unit, Circuit, [SERVER], [DATABASE]) AS
    (
        SELECT DISTINCT
            rer.Recipe,
            rer.RecipeType,
            tc.Translation,
            eqr.EQ_DestDescription,
            reorv.Circuit,
            @@SERVERNAME,
            DB_NAME()

        FROM dbo.tvf_rp3_REUnitProcedureByTime(@StartDT, @EndDT) reup
            JOIN (
                SELECT 'Line' as UT,* FROM dbo.tvf_rp3_EQUnit('CIPLCMN', NULL)
                UNION
                SELECT 'Tank' as UT,* FROM dbo.tvf_rp3_EQUnit('CIPTCMN', NULL)
            ) equ ON reup.EQ_UN_ID = equ.EQ_UN_ID
            JOIN dbo.tvf_rp3_RERecipe(@Area) rer ON reup.RE_RC_ID = rer.RE_RC_ID
            JOIN dbo.tvf_rp3_REOperationReportValues(NULL, NULL, NULL, @StartDT, @EndDT) reorv ON reup.RE_UP_ID = reorv.UPID AND reorv.Circuit IS NOT NULL
            JOIN dbo.tvf_rp3_TranslateCollection('UnitType', @Language) tc ON equ.UT = tc.Name
            JOIN dbo.EQ_Route eqr ON eqr.EQ_UN_ID_Manager = equ.EQ_UN_ID AND eqr.CipCircuit = reorv.Circuit
    )
    -- select all from CTE
    SELECT * FROM filterData
    -- add empty
    UNION
    SELECT NULL, NULL, NULL, NULL, NULL, @@SERVERNAME, DB_NAME()
    WHERE (SELECT COUNT(*) FROM filterData) = 0

END
GO

As you can see, there is no difference in the code, they are copied from the same file

So, what makes the performance difference?