Running SP versus SQL Statements

Folks:

I need some help with this. I have a stored procedure which if I run as :

exec test_sptma34 @.asofdate = '10/31/2007', @.analysisid = 'TVE-2004-10'

would NOT Complete at all and goes in a loop looks like (waited for 12 mins and then cancelled). Whereas If I run the same statements which are in SP then I get around 41 rows and that too within 1 to 2 seconds. Am I missing anything here?

STORED PROCEDURE:
==================
Code: ( sql )

CREATE PROCEDURE test_spTMA34(@.asofdate DATETIME, @.analysisid VARCHAR(50))AS SET NOCOUNT ON BEGIN declare @.ShiftData TABLE([BP Shift] float, [Elasticity] float, [Convexity] float) INSERT INTO @.ShiftData ([BP Shift], Elasticity, Convexity)SELECT cast(substring(D1.datatype, 12, len(D1.datatype)-13) AS float) AS '[BP Shift]', SUM(S.Weight*D1.value) AS Elasticity, SUM(S.Weight*D2.Value) AS ConvexityFROM tblData D1JOIN tblData D2 ON D1.asofdate = D2.asofdate AND D1.ticker = D2.ticker AND RIGHT(D1.datatype,LEN(D1.DataType) - 10) = RIGHT(D2.datatype, LEN(D2.DataType) - 9)JOIN tblPortfolio P ON P.ticker = D1.tickerJOIN tblSBMIWeights S ON S.ticker = D1.tickerWHERE P.portfolio = 'SBMI-TBA' AND P.asofdate = (SELECT MAX(asofdate) FROM tblPortfolio WHERE portfolio = 'SBMI-TBA' AND asofdate <= @.asofdate)AND S.asofdate = (SELECT MAX(asofdate) FROM tblSBMIWeights WHERE asofdate <=@.asofdate)AND D1.datatype LIKE 'Elasticity@.%' AND D1.DataSource = @.analysisidAND D2.datatype LIKE 'Convexity@.%' AND D2.DataSource = @.analysisidAND D1.asofdate = @.asofdateGROUP BY D1.datatype, D2.datatype INSERT INTO @.ShiftData ([BP Shift], Elasticity, Convexity)SELECT 0 AS '[BP Shift]', SUM(S.Weight*D1.value) AS Elasticity, SUM(S.Weight*D2.Value) AS ConvexityFROM tblData D1JOIN tblData D2 ON D1.asofdate = D2.asofdate AND D1.ticker = D2.ticker AND RIGHT(D1.datatype,LEN(D1.DataType) - 10) = RIGHT(D2.datatype, LEN(D2.DataType) - 9)JOIN tblPortfolio P ON P.ticker = D1.tickerJOIN tblSBMIWeights S ON S.ticker = D1.tickerWHERE P.portfolio = 'SBMI-TBA' AND P.asofdate = (SELECT MAX(asofdate) FROM tblPortfolio WHERE portfolio = 'SBMI-TBA' AND asofdate <= @.asofdate)AND S.asofdate = (SELECT MAX(asofdate) FROM tblSBMIWeights WHERE asofdate <=@.asofdate)AND D1.datatype = 'Elasticity' AND D1.DataSource = @.analysisidAND D2.datatype = 'Convexity' AND D2.DataSource = @.analysisidAND D1.asofdate = @.asofdateGROUP BY D1.datatype, D2.datatype SELECT * FROM @.ShiftDataORDER BY [BP Shift] END

================================================== =======

SAME SQL STATEMENTS (this runs within 1 to 2 secs and returs around 41 rows which is right)
================================================== =====
Code: ( sql )

SET NOCOUNT ONBEGINdeclare @.asofdate DATETIME, @.analysisid VARCHAR(50) SELECT @.asofdate = '10/31/2007'SELECT @.analysisid = 'TVE-2004-10' declare @.ShiftData TABLE([BP Shift] float, [Elasticity] float, [Convexity] float) INSERT INTO @.ShiftData ([BP Shift], Elasticity, Convexity)SELECT cast(substring(D1.datatype, 12, len(D1.datatype)-13) AS float) AS '[BP Shift]', SUM(S.Weight*D1.value) AS Elasticity, SUM(S.Weight*D2.Value) AS ConvexityFROM tblData D1JOIN tblData D2 ON D1.asofdate = D2.asofdate AND D1.ticker = D2.ticker AND RIGHT(D1.datatype,LEN(D1.DataType) - 10) = RIGHT(D2.datatype, LEN(D2.DataType) - 9)JOIN tblPortfolio P ON P.ticker = D1.tickerJOIN tblSBMIWeights S ON S.ticker = D1.tickerWHERE P.portfolio = 'SBMI-TBA' AND P.asofdate = (SELECT MAX(asofdate) FROM tblPortfolio WHERE portfolio = 'SBMI-TBA' AND asofdate <= @.asofdate)AND S.asofdate = (SELECT MAX(asofdate) FROM tblSBMIWeights WHERE asofdate <=@.asofdate)AND D1.datatype LIKE 'Elasticity@.%' AND D1.DataSource = @.analysisidAND D2.datatype LIKE 'Convexity@.%' AND D2.DataSource = @.analysisidAND D1.asofdate = @.asofdateGROUP BY D1.datatype, D2.datatype INSERT INTO @.ShiftData ([BP Shift], Elasticity, Convexity)SELECT 0 AS '[BP Shift]', SUM(S.Weight*D1.value) AS Elasticity, SUM(S.Weight*D2.Value) AS ConvexityFROM tblData D1JOIN tblData D2 ON D1.asofdate = D2.asofdate AND D1.ticker = D2.ticker AND RIGHT(D1.datatype,LEN(D1.DataType) - 10) = RIGHT(D2.datatype, LEN(D2.DataType) - 9)JOIN tblPortfolio P ON P.ticker = D1.tickerJOIN tblSBMIWeights S ON S.ticker = D1.tickerWHERE P.portfolio = 'SBMI-TBA' AND P.asofdate = (SELECT MAX(asofdate) FROM tblPortfolio WHERE portfolio = 'SBMI-TBA' AND asofdate <= @.asofdate)AND S.asofdate = (SELECT MAX(asofdate) FROM tblSBMIWeights WHERE asofdate <=@.asofdate)AND D1.datatype = 'Elasticity' AND D1.DataSource = @.analysisidAND D2.datatype = 'Convexity' AND D2.DataSource = @.analysisidAND D1.asofdate = @.asofdateGROUP BY D1.datatype, D2.datatype SELECT * FROM @.ShiftDataORDER BY [BP Shift]END

====================Question moved to SQL Server Forum.