|
|||||||||
|
|||||||||
|
|||||||||
 |
|||||||||
| |
||
|
|
|
|
|||||||||
|
|||||||||
|
|||||||||
|
|||||||||
| |
||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
ASP Free Forums Sponsor:
|
|
|
|
#1
November 24th, 2004, 03:45 PM
|
|||
|
|||
|
LIKE in a NULL field
Hi everyone,
I need to create a stored procedure that receives 7 paramaters, 6 of them are optional. The problem is that I am using a LIKE @param1 and sometimes the value in the tables for this @param1 is NULL. If the value the user provides for @param1 is null, that row is not going to be selected as the LIKE '%' for a NULL value is not selected. I could check for each param if @param1 is null, but since there are 6 of them, it is not practical to do so. I also tried adding LIKE @param1 OR @param1 is null, but then it causes me a problem when a value is provided cause it selects all the rows that has @param1 as NULL!! I really don't know how to deal with this. Using SQL Server 2000 and C# ASP.NET Thank you!
|
|
#2
November 24th, 2004, 04:00 PM
|
|||
|
|||
|
2 ways I can think of...
Well, probably more than 2 ways, but here goes:
Code:
IF @param1 IS NULL BEGIN SELECT ... FROM .... WHERE param1_column is null END ELSE BEGIN SELECT ... FROM ... WHERE param1_column LIKE '%" + @param1 + '%' END That attacks your problem directly, but lengthens your code and may cause issues with how well SQL caches the execution plan for your proc. My other idea is: Code:
DECLARE PROCEDURE @param1 varchar(10) = 'null' AS BEGIN SELECT ... FROM ... WHERE isnull(param1_column, 'null') like '%' + @param1 + '%' END But that may not work as well depending on how the calling app takes it. Try either one, and I'm curious to see if they work. Hope that helps, Steve Last edited by MrData : November 24th, 2004 at 04:01 PM. Reason: Forgot we're dealing with LIKE here...sorry
|
|
#3
November 25th, 2004, 10:21 AM
|
|||
|
|||
|
If when you say @param1 is null you mean is = '' then
select... from ... where ((field1 is null and @param1 = '') or field1 like '%' + @param1 + '%')
|
|
| Viewing: ASP Free Forums > Database > Microsoft SQL Server > LIKE in a NULL field |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
|
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
