Select All at top of queried parameter list

• GF

  Hall of Fame

  Points: 3774

  More actions

  September 29, 2011 at 12:22 pm

  #259391

  To all,

  I have a parameter called "State" for U.S. States.

  I have a query that pulls all the states from a state table.

  Select stateName, stateId from USStates order by stateName

  This populates the drop down list just fine.

  what I need is a way to have the words "Select All" at the top of the drop down so the user can get results for all US States and is not forced to select a state.

  I am not using check boxes but a drop down box.

  Can someone show me how to do this?

  Thanks

  Gary

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  September 29, 2011 at 12:52 pm

  #1388586

  Select stateName, stateId

  union all

  select '--Select All--', null

  from USStates order by stateName

  That will get you the "empty" row for select all.

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• GF

  Hall of Fame

  Points: 3774

  More actions

  October 8, 2011 at 10:14 pm

  #1392158

  Sean Lange,

  Thank you for your help.

  This worked for me.

  GF