MS SQL Server Query

I want to build a MS SQL Server query that gives the user the option to enter a value or simply select all values. Here is what I mean. I have a table Items.

I want to build a query that will give the user the opportunity to enter an actual value (ex. apples, carrots, etc) or simply enter/select 'All'.
Benki CanosoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
Assuming you are working in T-SQL but it could be adjusted to, c#, etc.

Declare @Item varchar(25);
Set @Item = '- all -'
Select Items Form Table1 Where Items = IIF(@Item = '- all -', Items, @Item)

This will give you all, the same SQL below will give you Apple only:
Set @Item = 'Apple'
Select Items Form Table1 Where Items = IIF(@Item = '- all -', Items, @Item)

For more on IIF() see: it is good in 2012 and 2014. It maybe deprecated in the new releases for and c#


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dung DinhDBA and Business Intelligence DeveloperCommented:

I suggest you create one store procedure as below
IF OBJECT_ID('usp_SearchItems') IS NOT NULL
   DROP PROCEDURE usp_SearchItems
@ItemList varchar(4000)=''
    DECLARE @sqlStr varchar(4000)
    SET @sqlStr = ' SELECT Name FROM dbo.Items '
                  + CASE @ItemList WHEN 'All' THEN ' WHERE 1=1' -- Search All
                                   WHEN '' THEN ' WHERE 1 <> 1' -- No search
                                   ELSE ' WHERE Name IN ('+ @ItemList +')' END
    EXEC (@sqlStr)                                  

Open in new window

Purpose of this store procedure is to process 3 cases:
+ Search all of items ==> Pass @ItemList ='All'
+ Search specific items ==> Pass @ItemList ='''Apples'',''Lemon'''
+ No search
EXEC usp_SearchItems @ItemList ='All'
EXEC usp_SearchItems @ItemList =''
EXEc usp_SearchItems @ItemList ='''Apples'',''Lemon'''

Open in new window

Another way, If you are using SQL 2008 or higher, you can use Table-Valued Parameters

(Name varchar(100))
IF OBJECT_ID('usp_SearchItemsByTableValuedParameter') IS NOT NULL
   DROP PROCEDURE usp_SearchItemsByTableValuedParameter
CREATE PROCEDURE usp_SearchItemsByTableValuedParameter
@ItemList AS FruitItem  READONLY
    SELECT i.Name FROM dbo.Items i
    INNER JOIN @ItemList p ON i.Name = p.Name

DECLARE @ItemList As FruitItem
INSERT INTO @ItemList VALUES('Apples'),('Orange')

EXEC usp_SearchItemsByTableValuedParameter @ItemList

Open in new window

For more detail Table-Valued Parameter
Vitor MontalvãoMSSQL Senior EngineerCommented:
This will returns all records from the table Items plus a new record "ALL":
SELECT name 
FROM Items

Open in new window

Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
You need a hierarchical model to do this properly.  SInce there is little data you can use adjacency list method.

Here is an example of structure you can use..

fruits: name(All, Carrots), type (Can either be Fruit or Fruit Group)
fruit_included: fruit_included(Foreign Key to fruits), fruit_group(associate a fruit to a specific)

You will be able to query based on either the selection of an individual item or all items belonging to a group...

Hope this helps.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.