sql query for select * from TableName where Type='*'

I am having a table Items as written below

Id,Name,Type

Possible values of row Type =a,b,c

On a 开发者_开发知识库webpage I have created dropdown(<select>) , with option, a,b,c,all , user can choose any value and then submit the page which eventually runs a a query and displays results to user.

Query for this

select * from Items where Type=@Type

This works fine when type =a,b,c but doesn't work when type = all

I know for type='All' i can do select * from Table , but i want to use the same above query.

How to write that query??

I am not looking for any-type of logical action to be performed on server(IIS) side logic, like

if (type=="all")
{
   //Use query 1
}
else
{
   //Use Uqery2
}

I want all logic to be performed within the query.

select * from Items
where ((lower(@Type) = 'all') or (Type = @Type))

If @Type can't be compared against a varchar 'all', use null instead, e.g.

select * from Items
where ((@Type is null) or (Type = @Type))

You could add a dummy TypeAll column to a subquery and filter on that.

SQL Statement

SELECT  Id, Name, Type
FROM    (
          SELECT  *
                  , TypeAll = 'all'
          FROM    Items
        ) q
WHERE   Type = @Type
        OR TypeAll = @Type        

Test Script

DECLARE @Type VARCHAR(32) = 'all'

;WITH Items (Id, Name, Type) AS (
  SELECT  1, 'Test', 'a'
  UNION ALL SELECT 2, 'Test', 'b'  
)
SELECT  Id, Name, Type
FROM    (
          SELECT  *
                  , TypeAll = 'all'
          FROM    Items
        ) q
WHERE   Type = @Type
        OR TypeAll = @Type        

I see you dont want an if else statement

in that case i personally would change the dropdown lists values to

a  value "=a"
b  value "=b"
c  value "=c"
all value "is not null"

then in your sql

select * from Items where Type @Type

or

select * from Items where Type like @Type

with the value for @type if its all being % ?