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 |
|
threeo
Starting Member
1 Post |
 Posted - 2009-11-06 : 15:25:40
|
| i have been struggling for almost a whole week now on a single issue for SQL. i have even hired 2 consultants ($40/hr) to see if they could solve it but no one has been able to.my problem involves how to handle NULL input parameters to a SQL query. BUT.....it also revolves around a third-party app's consumption of the query. for some reason, it can't handle the COALESCE function. what i HAVE gotten to work is this:and ps.SectionID in(select SectionID from {databaseOwner}{objectQualifier}aspdnsf_Section where (Name in(@prodtype) or NULLIF(@ProdType,'') IS NULL))this actually works. and by "works", i mean.....i can send in an actual value for prodtype, in which case the recordset reflects only those records with the prodtype......OR i can send a NULL for that parameter and the recordset will consist of all records.but what i STILL can not do......is have more than one of these types of parameters. for example, i'd like to now add:and c.CategoryID in(select CategoryID from {databaseOwner}{objectQualifier}aspdnsf_Category where (Name in(@region) or NULLIF(@region,'') IS NULL))but what happens is......i get an empty record set. i can either have one or the other statement but not both (there are records which meet both conditions)ANYONE have ANY suggestions? |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-11-07 : 07:45:44
|
What datatypes are involved?@prodtype?aspdnsf_Section?@region?aspdnsf_Category?What is an example value for @prodtype?What is an example value for @region?Are there other parts in your not shown WHERE-clause which can prevent the expected result?
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
|
|
|
i have been struggling all week with one SQL prob