Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
macca
Posting Yak Master
146 Posts |
 Posted - 2007-09-12 : 06:44:33
|
| I am passing 6 parameters into a stored procedure to use to search a database table for records. I want to return the record which contains the data passed in by the 6 parameters. I want that if any of the parameters are blank then it is ignored in the query and the query only returns results based on the parameters that contain data. The parameters are coming from a ASP.net search form where there are 6 fields and the user can decide to populate any number of the fields and then do the search. In other words I just want to do a basic search of a database table using 6 parameters which will vary in value.Anyone know how to do this?macca |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2007-09-12 : 06:49:13
|
execution plan will suck but here you go...select ....from tablenamewhere field1=coalesce(nullif(@param1,' '),field1)and field2=coalesce(nullif(@param2,' '),field2)....if @param1 is blank, it'll interpret it as null then takes in the value of the corresponding fieldso the above will take in all records if @param1 and @param2 are both blanks... to remove nullif and incase you really have a blank value, pass null (hope you don't have null values)  --------------------keeping it simple... |
 |
|
|
macca
Posting Yak Master
146 Posts |
Posted - 2007-09-12 : 07:27:32
|
| Jen,Thanks for the help.I have tried this out for 2 parameters to begin with but it is returning nothing. Even if I have values in for the parameters it still returns nothing. Here is the SQL I am using, am I doing something wrong?CREATE PROCEDURE sproc_SearchDisplay(@DateFrom Datetime,@DateTo Datetime,@Name varchar(150),@BehalfOf varchar(150),@RefNo varchar(10),@DeptId int)ASSELECT cheq_id, cheq_daterec, cheq_pername, cheq_amt As cheq_amtFROM RecCheqsWHERE cheq_behalf = coalesce(nullif(@BehalfOf, ' '),cheq_behalf)AND cheq_dept = coalesce(nullif(@DeptId, ' '),cheq_dept)Note:'cheq_behalf' and 'cheq_dept' are two fields in the table. |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2007-09-12 : 09:26:03
|
| and the values you're passing? if they're blanks you need the space otherwise it'll check for strings with blanks, deptID should be 0 or whatever value you assigned as non-existenttry to pass null--------------------keeping it simple... |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2007-09-12 : 09:27:04
|
quote:
Originally posted by jen
and the values you're passing? if they're blanks you need the space otherwise it'll check for strings with blanks, deptID should be 0 or whatever value you assigned as non-existenttry to pass null--edit: with values...then criteria fails--------------------keeping it simple...
--------------------keeping it simple... |
|
|
|
|
|
|
|
|
Searching data