Help Please

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.

 

Answer 1

User269602965 posted

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

Answer 2

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

Answer 3

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)