Solved

MS SQL Server Query

Posted on 2014-11-30
4
142 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
4 Comments
 
LVL 33

Accepted Solution

by:
Mike Eghtebas earned 125 total points
Comment Utility
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 7

Assisted Solution

by:Dung Dinh
Dung Dinh earned 125 total points
Comment Utility
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 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 125 total points
Comment Utility
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
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

744 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now