SQL Server: "All Values" option in Non-queried parameter

To add the option "All" in a Non-queried SQL parameter change your dataset like this:

SELECT statecodename, statecode
FROM FilteredOpportunity AS CRMAF_FilteredOpportunity
WHERE (accountid = @CRM_Accountid) AND (statecode = @CRM_Opp_Statt) OR
(accountid = @CRM_Accountid) AND (@CRM_Opp_Statt = - 1)

Next step is to add the option "All" to your Non-queried parameter:

 
 
 
 
 
 
 
 
 
 
 
 
 


Works like a charm!
 
Original post: http://timothychenallen.blogspot.com/2007/06/sql-server-all-values-parameters-in.html