quote:

---

Originally posted by shlms78

Just Curious,

Is the SP you are currently having problems with similar in design to the original (Call the stored proc and execute one select or else execute another select)? In other words, the SP can return different result sets based on a parameter switch...

Yak In the Box

---

CREATE   PROCEDURE [dbo].[spSelectChangeResults]
@DatasetId int,
@IndicatorId int, 
@AdditionalGeographicEntities varchar(200),
@UserId int,
@SiteId int,
@ApplicationId int = 5,
@IndicatorChangeTitle  varchar(500) OUTPUT,
@NumberOfYears int  OUTPUT,
@NumberOfGeographicEntities int  OUTPUT

AS



--Determine: What Geographic Entities can the user see?

DECLARE @GeographicEntitiesToUse TABLE
(
	[GeographicEntityId] [int] NOT NULL 
) 



INSERT INTO @GeographicEntitiesToUse
SELECT     tblGeographicEntitiesToUse.[value]
FROM         fnSplit(@AdditionalGeographicEntities, ',') tblGeographicEntitiesToUse INNER JOIN
                      tblGroupApplicationGeographicEntities ON tblGeographicEntitiesToUse.[value] = tblGroupApplicationGeographicEntities.GeographicEntityId INNER JOIN
                      tblGroups ON tblGroupApplicationGeographicEntities.GroupId = tblGroups.GroupId INNER JOIN
                      tblUserGroups ON tblGroups.GroupId = tblUserGroups.GroupID
WHERE     (tblUserGroups.UserID = @UserID) AND (tblGroups.SiteId = @SiteId) AND
 (tblGroupApplicationGeographicEntities.ApplicationId = @ApplicationId)


--Out of this list of GEs. Determine: 
--For Each Year in the resultset what is the most recent datarelease for geographic entities at this Spatial Level 
--that is viewable to this site?
DECLARE @tblMostRecentDataReleases  TABLE(
	[SpatialLevelId] [int] NOT NULL ,
	[IndicatorId] [int] NOT NULL ,
	[Year] [int] NOT NULL ,
	[DataReleaseId] [int] NULL 
) 

INSERT INTO @tblMostRecentDataReleases
SELECT     tblGeographicEntities.SpatialLevelId, tblMatrix.IndicatorId, tblMatrix.[Year], MAX(tblMatrix.DataReleaseId) AS DataReleaseId
FROM         tblSiteDataReleases INNER JOIN
                      tblMatrix ON tblSiteDataReleases.DataReleaseId = tblMatrix.DataReleaseId INNER JOIN
                      tblGeographicEntities tblGeographicEntities_1 ON tblMatrix.GeographicEntityId = tblGeographicEntities_1.GeographicEntityId INNER JOIN
                      tblGeographicEntities INNER JOIN
                      @GeographicEntitiesToUse [@GeographicEntitiesToUse] ON tblGeographicEntities.GeographicEntityId = [@GeographicEntitiesToUse].GeographicEntityId ON 
                      tblGeographicEntities_1.SpatialLevelId = tblGeographicEntities.SpatialLevelId
WHERE     (tblSiteDataReleases.SiteId = @SiteId) AND  (tblMatrix.IndicatorId = @IndicatorId)
GROUP BY tblGeographicEntities.SpatialLevelId, tblMatrix.IndicatorId, tblMatrix.[Year]




DECLARE @tmpResults TABLE (
	[GeographicEntityId] [int] NOT NULL ,
	[GeographicEntityName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Year] [int] NOT NULL ,
	[IndicatorValue] [float] NULL 
) 

INSERT INTO @tmpResults
SELECT     tblMatrix.GeographicEntityId, tblGeographicEntities.GeographicEntityName, tblMatrix.[Year], tblMatrix.IndicatorValue
FROM      @tblMostRecentDataReleases   [@tblMostRecentDataReleases] INNER JOIN
                      tblMatrix ON [@tblMostRecentDataReleases].IndicatorId = tblMatrix.IndicatorId AND 
                      [@tblMostRecentDataReleases].DataReleaseId = tblMatrix.DataReleaseId AND [@tblMostRecentDataReleases].[Year] = tblMatrix.[Year] INNER JOIN
                      tblGeographicEntities ON tblMatrix.GeographicEntityId = tblGeographicEntities.GeographicEntityId
INNER JOIN @GeographicEntitiesToUse [@GeographicEntitiesToUse] ON 
tblGeographicEntities.GeographicEntityId = [@GeographicEntitiesToUse].GeographicEntityId




--In the event that we have 2 Geographic Entities with the same name but different IDs use a concatenation of the 2 so that 
--the CrossTab data provider can tell them apart.
SELECT     COALESCE (tblNonDuplicated.GeographicEntityName, [@tmpResults].GeographicEntityName + ' - ' + CONVERT(VARCHAR(6), 
                      [@tmpResults].GeographicEntityId)) AS GeographicEntityName, [@tmpResults].[Year], [@tmpResults].IndicatorValue
FROM         (SELECT     GeographicEntityName
                       FROM        @tmpResults  [@tmpResults]
                       GROUP BY GeographicEntityName
                       HAVING      (COUNT(DISTINCT GeographicEntityId) = 1)) tblNonDuplicated RIGHT OUTER JOIN
                    @tmpResults  [@tmpResults] ON tblNonDuplicated.GeographicEntityName = [@tmpResults].GeographicEntityName
ORDER BY [@tmpResults].[Year], tblNonDuplicated.GeographicEntityName

--Sort out all Output Params

SELECT  @IndicatorChangeTitle = IndicatorChangeTitle
FROM         tblIndicators
WHERE     (IndicatorId = @IndicatorId)

SELECT  @NumberOfYears = COUNT(DISTINCT [Year]), @NumberOfGeographicEntities = COUNT(DISTINCT GeographicEntityId)
FROM         @tmpResults

quote:

---

Originally posted by MartinSmith

I'm unsure whether this or the resultant recompile made the difference but all started working well again.

---

quote:

---

Originally posted by spirit1

you're probably experiencing what is known as parameter sniffing.
http://blogs.msdn.com/khen1234/archive/2005/06/02/424228.aspx

---