Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2003
    Posts
    1

    Unanswered: 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!

    Cris

  2. #2
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Re: problem maybe with IN

    if you dynamically build your query
    and then execute it

    will that do ?

  3. #3
    Join Date
    Nov 2003
    Posts
    48

    Re: problem maybe with IN

    The query can be

    Code:
    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
    Shianmiin

  4. #4
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Re: problem maybe with IN

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

    nice solution !

  5. #5
    Join Date
    Sep 2003
    Posts
    522
    Code:
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •