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 NULL
.
You can also do what we've done here.
Take a look at the WHERE
clause of our stored procedure dbo.uspSalesPersonSalesByYear
.
WHERE ('ALL' IN (@SalesTerritory)) OR (pvt.SalesTerritory IN (@SalesTerritory))
That's it.
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.
18.191.176.194