Taking the original query as below the TerritoryID is numeric. The workaround you suggested with convert(varchar(xx),TerritoryID) would work but may cause issues with sorting the 'ALL' into the correct position in the parameter list.

SELECT DISTINCT TerritoryID

FROM Sales.Customer

UNION

SELECT ' All' AS TerritoryID

FROM Sales.Customer

ORDER BY TerritoryID

Usually the territory will have a territory table (i.e. 1:M relationship to Sales.Customer). This table may hold a name for the territory as well as the ID.

Choice 1 : Show all territories regardless of whether any sales customers exist. The query below would return the parameter list with the TerritoryID, Name and sortorder which is used to force the '-ALL-' to the top of the list.

SELECT TerritoryID, TerritoryName, 1 as SortOrder

FROM Sales.SalesTerritory

UNION

SELECT -99, '-ALL-', 0

ORDER BY SortOrder, TerritoryName

Choice 2 : Show only territories where sales customers exist. The query below would return the parameter list with the TerritoryID, Name and sortorder which is used to force the '-ALL-' to the top of the list.

SELECT ST.TerritoryID, ST.TerritoryName, 1 as SortOrder

FROM Sales.SalesTerritory as ST

WHERE EXISTS (

SELECT TerritoryID FROM Sales.Customers as SC WHERE ST.TerritoryID = SC.TerritoryID

)

UNION

SELECT -99, '-ALL-', 0

ORDER BY SortOrder, TerritoryName

----------------------------------

A similar technique can be used for NULL values.

SELECT TerritoryID, TerritoryName, 2 as SortOrder

FROM Sales.SalesTerritory

UNION

SELECT -99, '-ALL-', 0

ORDER BY SortOrder, TerritoryName

UNION

SELECT -98, '-NULL-', 1

ORDER BY SortOrder, TerritoryName

This would lead the report query to have :

SELECT *

FROM Sales.Customer

WHERE

(TerritoryID = @TerritoryID OR @TerritoryID = -99)

OR

(TerritoryID IS NULL AND @Territory = -98)

----------------------------------

You can use the '-ALL-' in cascaded parameters.

Example:

Show all selected products where the subcategory (whether specific or all) leads to the colors available then both parameters are used in the report query.

(Parameter) Subcategory ... (Parameter) Color ... (Report) Products

1. Subcategory_Parameter_DataSet

SELECT Name, SubCategoryID, 1 as SortOrder

FROM Production.ProductSubcategory

UNION

SELECT '-ALL-',-99,0

ORDER BY SortOrder, SubcategoryID

2. Color_Parameter_DataSet

SELECT DISTINCT Color, 1 as SortOrder

FROM Production.Product

WHERE ProductSubcategoryID = @Subcategory

UNION

SELECT '-ALL-', 0

ORDER BY SortOrder, Color

3. ProductList_Report_DataSet

SELECT *

FROM Production.Product

WHERE

(ProductSubcategoryID = @Subcategory OR @Subcategory = -99)

AND

(Color = @Color OR @Color = '-ALL-)

Hope this helps.

Fitz