This one has me a bit stumped... I have NEVER seen this before...
I have stored procedure
ALTER PROCEDURE dbo.usp_Create_Fact_Job (@startDate date, @endDate date) AS
--DECLARE @startDate date
--DECLARE @endDate date
--SET @startDate = '01 APR 2014'
--SET @endDate = '02 APR 2014'
/*-- end of Debug*/
WITH CTE_one AS ( blah blah blah)
SELECT a whole bunch of fields from the joined tables and CTEs
When I run the code inside the stored procedure by Declaring and setting the start and enddates manually the code runs in 4 minutes (missing some indexes ;-) )
When I call the stored procedure with the ExEC
DECLARE @return_value int
EXEC @return_value = [ClaimCenter].[usp_Create_Fact_Job]
@startDate = '01 apr 2014',
@endDate = '01 apr 2014'
SELECT 'Return Value' = @return_value
It never returns a results set but doesn't error out either. I have left it for 40 minutes and still no joy.
Any idea what is going on as I am stumped as to why it never returns a result
The sproc is reasonably complicated; 6 CTEs to find the most recent version of records and some
2 joins to parent tables (parent and grandparent), 3 joins to child tables (child, grandchild and great grandchild) and 3 joins to lookup views each of which self references a table to filter for last version of a record.