Solved

MS SQL Server Query

Posted on 2014-11-30
4
158 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 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 7

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 48

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Download ms sql express. 2 28
Index and Stats Management-Specific tables 8 22
SQLCMD Syntax 2 15
.NET Enums [Flags] & Bitwise Design Question 6 28
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

829 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