There are other options on how to add
ALL as a variable to return the whole dataset in a single parameter. Normally, you would do this on the dataset query:
SELECT 'ALL' as col1, NULL as col2, NULL, as col3 UNION ALL SELECT col1, col2, col3 FROM…
Then allow the parameter to accept
You can also do what we've done here.
Take a look at the
WHERE clause of our stored procedure
WHERE ('ALL' IN (@SalesTerritory)) OR (pvt.SalesTerritory IN (@SalesTerritory))
If you use the
ALL parameter in combination with a multivalued parameter, you need to use the
MultiValueParamSplit function as explained in Chapter 1, Let's Breakdown the Numbers.