Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 169
  • Last Modified:

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.
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
Benki Canoso
Asked:
Benki Canoso
4 Solutions
 
Mike EghtebasDatabase and Application DeveloperCommented:
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
 
Dung DinhDBA and Business Intelligence DeveloperCommented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
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.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now