where value as * - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

where value as *

Author

Topic

devilsuraj
Starting Member

1 Post

Posted - 2011-09-21 : 01:58:31

hi
i am runing a query to select data from table where country as submited

i know its a simple query
Select * from table where country = "given value" & type ="given value"

but can i make this to run like this
Select * from table where country = "*" & type ="given value"
and will it work to show all the data from table for all countries?
countries value will be submited from dropdownbox so if user selects all so it should show data for all countries

i am a noob so may be i am wrong but if there is any simple solution for this please tell me,

Ehan
Starting Member

19 Posts

Posted - 2011-09-21 : 04:43:04

this should work for you

Select * from table
where 1 = CASE WHEN "given value" = '*' THEN 1
WHEN country = "given value" THEN 1
ELSE 0 END
AND type ="given value"

Kristen
Test

22859 Posts

Posted - 2011-09-21 : 05:00:30

General routes to solve this are to either use dynamic SQL (and to leave out the "where country =..." when the user selects "All countries" from the dropdown, or to pass a NULL parameter when you want to match "all values":


SELECT COl1, Col2, ...
FROM MyTable
WHERE     (@MyCountry IS NULL OR country = @MyCountry)
      AND (@MyType IS NULL OR type = @MyType)

this uses a parameterised query, so you will have to define @MyCountry and @MyType - either directly using the query, or by executing sp_ExecuteSQL with your query and parameter list, or by creating a Stored Procedure.

If you are not going to use a stored procedure then using dynamic SQL to generate a WHERE clause containing only the columns the user has supplied criteria for will be the most efficient (but ideally you should still use a parameterised query as that will perform much faster)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources