not in

[Bogdan Rechi]

Hi,

I'm trying to select some stored procedures' names who cannot be found into
the 'FilterProcedur e' field of my table 'Lists':

select name
from sysobjects
where (type = 'p') and
(name not in
(select FilterProcedure from Lists
where FilterProcedure is not null))
order by name asc

'FilterProcedur e' is defined as nvarchar(256), exactly as the 'name'
field of the 'sysobjects'. But the "not in" condition is never respected,
even if the intersection between the tables on the 'FilterProcedur e' field
is not void.

Do you know what's wrong here?

Bogdan.

[Erland Sommarskog]

Re: not in

[posted and mailed, please reply in news]

Bogdan Rechi (bogdan_rechi@h otmail.com) writes:[color=blue]
> I'm trying to select some stored procedures' names who cannot be found
> into the 'FilterProcedur e' field of my table 'Lists':
>
> select name
> from sysobjects
> where (type = 'p') and
> (name not in
> (select FilterProcedure from Lists
> where FilterProcedure is not null))
> order by name asc
>
> 'FilterProcedur e' is defined as nvarchar(256), exactly as the 'name'
> field of the 'sysobjects'. But the "not in" condition is never respected,
> even if the intersection between the tables on the 'FilterProcedur e' field
> is not void.
>
> Do you know what's wrong here?[/color]

Not really. Usually is a NULL value that trips people with IN / NOT IN,
but you seem to have catered for that.

Nevertheless, try NOT EXISTS instead:

SELECT name
FROM sysobjects o
WHERE type = 'P'
AND NOT EXISTS (SELECT *
FROM Lists l
WHERE l.FilterProcedu re = o.name)
ORDER BY o.name ASC


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Bogdan Rechi]

Re: not in

>> Nevertheless, try NOT EXISTS instead:
Thank you for the tip but unfortunately it didn't work.

I've tried to walk the queries step by step, using 2 cursors:

---------------------------------

declare curs cursor for
select name
from sysobjects
where type = 'p'
order by name asc

declare curs1 cursor for
select FilterProcedure FROM Lists
where FilterProcedure is not null

open curs

declare @ProcedureName nvarchar (256)
declare @ProcedureName1 nvarchar (256)

FETCH NEXT FROM curs into @ProcedureName
WHILE @@FETCH_STATUS = 0
begin
open curs1
FETCH NEXT FROM curs1 into @ProcedureName1
WHILE @@FETCH_STATUS = 0
begin
if (@ProcedureName 1 = @ProcedureName) -- (!!!!)
print 'equal ' + @ProcedureName + ' and ' + @ProcedureName1
else
print 'not equal ' + @ProcedureName + ' and ' + @ProcedureName1
FETCH NEXT FROM curs1 into @ProcedureName1
end
close curs1
FETCH NEXT FROM curs into @ProcedureName
end

deallocate curs1

close curs
deallocate curs

-------------------------

The comparison at the line marked (!!!!) always returns false, even if the
values appear to be the same.(!?)

Now, if i would run high on arrogance i would say that it is an SQL-Server
obvious bug, but i rather have a poor experience with this system and i'm
still wondering about what's going on.

Bogdan.

[Hugo Kornelis]

Re: not in

On Thu, 2 Dec 2004 15:41:39 +0200, Bogdan Rechi wrote:

(snip)[color=blue]
>The comparison at the line marked (!!!!) always returns false, even if the
>values appear to be the same.(!?)
>
>Now, if i would run high on arrogance i would say that it is an SQL-Server
>obvious bug, but i rather have a poor experience with this system and i'm
>still wondering about what's going on.[/color]

Hi Bogdan,

This might be caused by trailing blanks, unprintable characters or other
such nastiness. You might wish to check out the length of the two
variables, and you could check the UNICODE() value for each individual
character in both strings.

If that is not it, try if you can create a repro and post it here.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Bogdan Rechi]

Re: not in

>> This might be caused by trailing blanks, unprintable characters or other
Yes, it was a CRLF at the end of each record i've entered. The Query
Analizer was very confusing because it automatically trimmed the strings
while printing.

Thank you all for the answers.

Bogdan.

[Hugo Kornelis]

Re: not in

On Thu, 2 Dec 2004 16:38:35 +0200, Bogdan Rechi wrote:
[color=blue][color=green][color=darkred]
>>> This might be caused by trailing blanks, unprintable characters or other[/color][/color]
>Yes, it was a CRLF at the end of each record i've entered. The Query
>Analizer was very confusing because it automatically trimmed the strings
>while printing.[/color]

Hi Bogdan,

QA will trim CRLF if you use the Results in Grid option. The Results in
Text do show CRLF.

If you don't refer to trimming CRLF, but trimming after the first 256
characters, have a look at Tools / Options / Results / Maximum characters
per column.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)