locked
Help Please RRS feed

  • Question

  • User-1699292883 posted

    Please i need a solution to this problem i have been battking with for over a week now.

    i am new to Sybase DB.

    I need a stored Procedure that will do a search with several Optional Parameters i have used several scripts but no Success.

     

    Wednesday, September 14, 2011 4:30 AM

Answers

  • User269602965 posted

    Try this boolean logic trick to ignore NULLs but use the bind variable if NULL

    If the @Firstname is NULL it returns the value 1 and 1 = 1 is TRUE so this will not filter the select values

    If the @Firstname IS NOT NULL then 1 = NULL is FALSE and the OR statement au_fname = @FirstName is applied as the filter

    then the same logic applies to the next filter condition and the next.

    Sybase may have another function for NVL (NVL is replace NULL with the following value  NVL(:BINDVAR,REPLACEMENTVALUE).

    Sybase should support NVL or have a similar function.

     

    select *
    from authors
    where
    (1 = NVL(@FirstName,1) OR au_fname = @FirstName)
    AND
    (1 = NVL(@LastName,1)  OR au_lname = @LastName)
    AND
    (1 = NVL(@State,1)     OR state = @State) 

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, September 17, 2011 10:48 PM

All replies

  • User269602965 posted

    forum users might be able to help if you provided more details.

    Wednesday, September 14, 2011 12:30 PM
  • User-1699292883 posted

    ok thanks.....

    i have 4 Textboxes on my webform and users are supoose to enter values into any one of the Textboxes or all.

    So the stored procedure is supposed to do a search with the parameters received from the textboxes and ignoring any NULL values supplied.

    i have tried this,

    select *
    from authors
    where (@FirstName IS NULL or au_fname = @FirstName)
    and   (@LastName IS NULL or au_lname = @LastName)
    and   (@State IS NULL or state = @State) 
    
    
    and other scripts but no  way.
    Please Help
    Wednesday, September 14, 2011 12:41 PM
  • User269602965 posted

    Try this boolean logic trick to ignore NULLs but use the bind variable if NULL

    If the @Firstname is NULL it returns the value 1 and 1 = 1 is TRUE so this will not filter the select values

    If the @Firstname IS NOT NULL then 1 = NULL is FALSE and the OR statement au_fname = @FirstName is applied as the filter

    then the same logic applies to the next filter condition and the next.

    Sybase may have another function for NVL (NVL is replace NULL with the following value  NVL(:BINDVAR,REPLACEMENTVALUE).

    Sybase should support NVL or have a similar function.

     

    select *
    from authors
    where
    (1 = NVL(@FirstName,1) OR au_fname = @FirstName)
    AND
    (1 = NVL(@LastName,1)  OR au_lname = @LastName)
    AND
    (1 = NVL(@State,1)     OR state = @State) 

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, September 17, 2011 10:48 PM