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.
Note
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.
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.