problem maybe with IN

Hi all,

I've a SP that retrieve a list of users. The problem is that on this SP i want return a list of all users or only based on a specific filter gived by the customer.

I don't want ot use IF statements to see if for example the variables @.username contains something or is null.

Ok, so.. there is a method that return all the users if the variables is null and a specific user is the variable contain something without use condition?

Please note also that is not only one variable but can be more (ie, filter by password and email too)

Thanks for the help!

Crisif you dynamically build your query
and then execute it

will that do ?|||The query can be

select ...
from ...
where (field1 = @.param1 or @.param1 is null)
and (field2 = @.param2 or @.param2 is null)

Originally posted by bortolo
Hi all,

I've a SP that retrieve a list of users. The problem is that on this SP i want return a list of all users or only based on a specific filter gived by the customer.

I don't want ot use IF statements to see if for example the variables @.username contains something or is null.

Ok, so.. there is a method that return all the users if the variables is null and a specific user is the variable contain something without use condition?

Please note also that is not only one variable but can be more (ie, filter by password and email too)

Thanks for the help!

Cris|||select ...
from ...
where (field1 = @.param1 or @.param1 is null)
and (field2 = @.param2 or @.param2 is null)

nice solution !|||create proc sp_return_users (
@.uid char(10) = null,
@.pwd varchar(16) = null,
@.eml varchar(128) = null)
as
declare @.tbl table (
uid char(10) null, @.pwd varchar(16) null, @.eml varchar(128) null)

insert @.tbl values (isnull(@.uid, '%'), isnull(@.pwd, '%'), isull(@.eml, '%'))

select u.* from dbo.tblusers u
inner join @.tbl t
on (u.uid like t.uid
and u.pwd like t.pwd
and u.eml like t.eml)
go