Solved

MS SQL Server Query

Posted on 2014-11-30
4
163 Views
Last Modified: 2014-12-02
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.
Items:
Apples
Carrots
Lemon
Orange

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'.
0
Comment
Question by:Benki Canoso
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 34

Accepted Solution

by:
Mike Eghtebas earned 125 total points
ID: 40472865
Assuming you are working in T-SQL but it could be adjusted to vb.net, 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: http://msdn.microsoft.com/en-us/library/hh213574.aspx it is good in 2012 and 2014. It maybe deprecated in the new releases for vb.net and c#



Mike
0
 
LVL 8

Assisted Solution

by:Dung Dinh
Dung Dinh earned 125 total points
ID: 40472993
Hi,

I suggest you create one store procedure as below
IF OBJECT_ID('usp_SearchItems') IS NOT NULL
   DROP PROCEDURE usp_SearchItems
GO   
CREATE PROCEDURE usp_SearchItems
@ItemList varchar(4000)=''
As
BEGIN
    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)                                  
END

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

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

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

EXEC usp_SearchItemsByTableValuedParameter @ItemList

Open in new window


For more detail Table-Valued Parameter
0
 
LVL 50

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 125 total points
ID: 40473247
This will returns all records from the table Items plus a new record "ALL":
SELECT name 
FROM Items
UNION ALL
SELECT 'All'

Open in new window

0
 
LVL 23

Assisted Solution

by:Racim BOUDJAKDJI
Racim BOUDJAKDJI earned 125 total points
ID: 40475526
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.
0

Featured Post

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question