CODEWITHSUNDEEP
sqlasp.netsql-server
BFry
BFry

Reputation: 915

How to cater Search from front-end in SQL Server query

I have an asp.net application. Functionality required is:

There is 1 dropdown and a gridview underneath. When the page is loaded for first time, nothing is selected in dropdown. The query works like:

Select * from myTable;

Then when we select something from the dropdown, the query is:

Select * from myTable where Col1 = @valueSelected

I want to use single query for both the cases, i.e

SELECT * FROM myTable
Where
Case @valueSelected WHEN NULL THEN Col1 IN ('ALL', 'PASSED', 'FAILED') ELSE Col1 = @valueSelected END

or

SELECT * FROM myTable
Where
Case @valueSelected WHEN NOT NULL THEN Col1 = @valueSelected ELSE NULL END

But both dont work as of now.

Any help/suggestions/pointers ?

Upvotes: 0

Views: 132

Answers (2)

Pரதீப்
Pரதீப்

Reputation: 93734

The syntax use used to simulate If else condition using Case statement in where clause is wrong.

If you need a specific list of values from Col1 then try this.

SELECT *
FROM   myTable
WHERE  ( Col1 IN ( 'ALL', 'PASSED', 'FAILED' )
         AND @valueSelected IS NULL )
        OR col1 = @valueSelected

If you want to have all the values in col1 then try this.

Select * from myTable where Col1 = coalesce(@valueSelected,Col1)

Upvotes: 2

Raghubar
Raghubar

Reputation: 2788

Try This.

Select * from myTable where Col1 = ISNULL(@valueSelected,Col1)

Upvotes: 1

Related Questions