| Author |
Topic |
|
asgast
Posting Yak Master
149 Posts |
 Posted - 2009-08-04 : 09:59:11
|
| Its more complicated then it sounds :)I have a front end application (I can't change it) in which users can enter data filter usually something like ccode = 1 AND job =2I have just added this filter to may WHERE part in dynamic sql, everything worked just fine.Now users started to enter filters like not(ccode = 1) and for this case I need to return rows where ccode IS NULL. And I don't know how to do it. I cant overwrite nulls.Any ideas?Is it possible? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-08-04 : 10:01:31
|
| this is a sure candidate for sql injection. can i ask why you've given a ui like this? it would be much better to give separate input boxes for each parameter and use it in filter |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-08-04 : 10:06:58
|
Post the actual query and explain what you want to change.
N 56°04'39.26"E 12°55'05.63" |
|
|
|
asgast
Posting Yak Master
149 Posts |
Posted - 2009-08-04 : 10:20:18
|
| create table #t(t int)insert #t(t)values (1)insert #t(t)values (2)insert #t(t)values (null)insert #t(t)values (null)DECLARE @n nvarchar(4000)DECLARE @w nvarchar(100)SET @w = ' not(t = 01)'SET @n = 'SELECT * FROM #t WHERE'+@wEXEC sp_executesql @nDROP TABLE #tThis is a simple sample usually the filter is longer and more complexI want to see rows that contain NULL, as far as I see it its impossible, so I hoped that someone on this forum might have an answer.I'm aware of sql injection possibility here it will not happen :) |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-08-04 : 10:27:32
|
This is t-sql code, not a query built in the application and sent to the database.
N 56°04'39.26"E 12°55'05.63" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-08-04 : 10:30:45
|
[code]create table #t(t int)insert #t(t)values (1)insert #t(t)values (2)insert #t(t)values (null)insert #t(t)values (null)DECLARE @n nvarchar(4000)DECLARE @w nvarchar(100)SET @w = ' not(t = 01) OR t IS NULL'SET @n = 'SELECT * FROM #t WHERE'+@wEXEC sp_executesql @nDROP TABLE #t[/code]
N 56°04'39.26"E 12°55'05.63" |
|
|
|
asgast
Posting Yak Master
149 Posts |
Posted - 2009-08-04 : 10:58:54
|
| :) This is so obvious that I forgot to tell I can't use it :)I can't tech sql basics to all the managers to get OR r IS NULL as a filter, I don't know what columns they will enter into the filter so I cant add OR case. I need null values only for the case when i get NOT(t=1) so always adding OR with all the names participating in the filter is not a possible solution.I usually get something like ((c=deu) and (j=16 OR j = 10)) or (l=en) AND (l = eng) I have come to a conclusion that its impossible. |
|
|
|
rocknpop
Posting Yak Master
201 Posts |
Posted - 2009-08-04 : 11:49:59
|
| Maybe you can try this on the client side in javascript than the backend. split the string,find NOT,add additional string, something on these lines or some other logic for manipulating this string. Should not say it's impossible.Let's wait what for the experts to answer this, maybe there is a way to handle this in sql.--------------------Rock n Roll with SQL |
|
|
|
jeremygiaco
Starting Member
14 Posts |
Posted - 2009-08-04 : 16:04:07
|
| Can you add the following to your t-sql code?SET @w = replace(@w,'not(t = 01)','not(isnull(t,0) = 01)')or slightly more generically:SET @w = replace(@w,'not(','not(isnull(')SET @w = replace(@w,' = ',',0) = ')Jeremy Giaco |
|
|
|
|
get null values for where not(column = 1)