conditional where statement

I have a stored procedure that performs a search function with params:

@.username nvarchar(50)
@.country nvarchar(50)
and like 10 more.

A user may provide values for these params optionally.
So when the @.username var is left blank, there should be no filtering on the username field (every field should be selected regardless of the username)
Currently my statement is:

select username,country from myUsers where
username=@.username andcountry=@.country

With this statement when a user provides no value for username the username field selects on ''m which returns ofcourse nothing...

What can I do to solve this?

Thanks!

SELECTFROM YourTableWHERE (@.usernameISNULL OR UserName = @.username )AND (@.countryISNULL OR Country = @.country )

|||

Thanks, but in this case the username field would not be ignored.
If someone has filled in a username, say "peter", but the webvisitor would not want to search on any username, the statement would be:

SELECT
FROM YourTable
WHERE (@.usernameISNULL OR UserName = '' )

In this case the user with name "peter" would not be found. If a webvisitor does NOT provide a username, I want to return all rows regardless of the value in the username field...
Im just hoping I've explained myself clearly now :)
Thanks!

|||

use WHERE (UserName = COALESCE(@.UserName, UserName)) AND (Country = COALESCE(@.Country, Country)) AND etc.

COALESCE (or ISNULL if you prefer) will return the first value in the parameter list that is not null, so if you pass a NULL value for, say, @.UserName, that part of the WHERE clause will resolve to "WHERE UserName = UserName", which of course, is always true.

|||

Peter Smith:

If someone has filled in a username, say "peter", but the webvisitor would not want to search on any username

Can you explain what you mean by that? If there is a value in @.username, it will be searched against, else ignored. If there is a value provided and it does not exist in the table, obviously nothing will be returned. Incase the query doesnt work as expected, please provide some sample data, and sample scenarios and their expected outputs.

|||

Dinakar Nethi provided an excellent query for your issue. The key to implement is:

You need to set your input parameter to default NULL first.

@.UserName NVarchar(50) = NULL,

@.Country NVarchar(50) = NULL

|||

mmm, I see (now). I tested your query and it works :)
Thanks!