Thanks for the suggestions -- it hadn't occurred to me to use a parameter tracking table like that. It's a useful tool. As regards the logic suggestion, my sproc was using something like that already, but thank you anyway.
The problem was in an unexpected place. The end result of the sproc was to return the following result set:
IF @intmg>0
BEGIN
SELECT 'All' AS RegionID
,'All Regions' AS Region
UNION ALL
SELECT DISTINCT Regionshort
,RegionShort
FROM dimdistributors
END
ELSE
BEGIN
SELECT DISTINCT Regionshort
,RegionShort
FROM dimdistributors
WHERE RegionShort=@strRegion
END
But the query in the ELSE statement (the one that 'wasn't working') did not alias the columns, so the dataset in the report wasn't being populated. When I added aliases as below, everything got cleaned up.
IF @intmg>0
BEGIN
SELECT 'All' AS RegionID
,'All Regions' AS Region
UNION ALL
SELECT DISTINCT Regionshort
,RegionShort
FROM dimdistributors
END
ELSE
BEGIN
SELECT DISTINCT Regionshort AS RegionID
,RegionShort AS Region
FROM dimdistributors
WHERE RegionShort=@strRegion
END
Thanks again! David