different results with the same query

I have SELECT query which include 2 UNION operators.
SELECT ....
UNION
(select....
UNION
selec...)as t1...
If I execute this query in query analyzer, I get 7 records as a result.
If I create new SP and copy the same query into this new STORED PROCEDURE
and execute this procedure,
I get 13 records as a result.
How is that possible that absolutly the same sintax return diferent results?
Both queries are executed on the same database with sa account.
It seems to me that if I execute this query in query analyzer, it return
results as without the union.
Does anybody know why?
This is the first time for me experiencing something like this in 7 years.
Regards,
Simonno It should not. check somebody might have updated mean while.
Post complete script if you find problem
--
Regards
R.D
--Knowledge gets doubled when shared
"simon" wrote:

> I have SELECT query which include 2 UNION operators.
> SELECT ....
> UNION
> (select....
> UNION
> selec...)as t1...
> If I execute this query in query analyzer, I get 7 records as a result.
> If I create new SP and copy the same query into this new STORED PROCEDURE
> and execute this procedure,
> I get 13 records as a result.
> How is that possible that absolutly the same sintax return diferent result
s?
> Both queries are executed on the same database with sa account.
> It seems to me that if I execute this query in query analyzer, it return
> results as without the union.
> Does anybody know why?
> This is the first time for me experiencing something like this in 7 years.
> Regards,
> Simon
>
>|||nobody changed anything.
I execut the procedure in query analyzer: exec dbo.test
OR I execute script below in query analyzer(without "create procedure
dbo.test as" text)
and I get different results.
Amazing, isn't it? Both scripts are executed in the same query analyzer
window with the Sa account butt different result.
Is there some bug or what?
I also try to execute the script with SQL2005 SQL server managment studio
but the same result.
The script is:
CREATE PROCEDURE dbo.test
AS
declare @.idIzdelka varchar(20),@.idDrzave char(3)
set @.idIzdelka='I2314'
set @.idDrzave='CZK'
SELECT
Tk.st_nar_dob,nerazdeljena=sum(Tk.nerazdeljena),razdeljena=sum(Tk.razdeljena
),skupaj=sum(Tk.skupaj),
dobava=(SELECT rok_dobave FROM narDobIzdNed WHERE st_nar_dob=tk.st_nar_dob
AND izd_id=@.idIzdelka)FROM
(SELECT
st_nar_dob=T2.navisionId,sum(isnull(T2.navKolicina,0))-sum(T2.nar_kolicina)-
sum(isnull(T2.zakljucenaKol,0))
as nerazdeljena,
sum(T2.nar_kolicina)+sum(isnull(T2.zakljucenaKol,0)) as
razdeljena,sum(isnull(T2.navKolicina,0)) as skupaj FROM
(SELECT T1.*,zalogaNavision=(SELECT kolicina FROM skladisceIzdelek WHERE
navisionId=t1.navisionId
AND idIzdelka=@.idIzdelka),
navKolicina=(SELECT isnull(kolicina_dej,kolicina_nar) FROM narDobIzdNed
WHERE st_nar_dob=t1.navisionId AND izd_id=@.idIzdelka),
zakljucenaKol=(SELECT sum(nar_kolicina) FROM narociloIzdelek WHERE
navisionID=t1.navisionId AND izd_id=@.idIzdelka and izd_zakljucen=1)
FROM
(SELECT sum(n2.nar_kolicina) as nar_Kolicina,n2.navisionId FROM
(select n1.nar_id,n1.izd_id,max(n1.datum_spremembe) as datumSpremembe FROM
narociloIzdelek n1
GROUP BY n1.nar_id,n1.izd_id)AS T1
INNER JOIN narociloIzdelek n2 ON T1.nar_id=n2.nar_id AND T1.izd_id=n2.izd_id
AND T1.datumSpremembe=n2.datum_spremembe
INNER JOIN narocilo n ON n2.nar_id=n.nar_id INNER JOIN skladisce s ON
n.nar_skladisce_id=s.skladisce_id
INNER JOIN uporabnik u ON u.up_id=n.nar_up_id
WHERE n.nar_status=2 AND n2.izd_zakljucen=0 AND n2.izd_id=@.idIzdelka and
n2.nar_kolicina<>0
and not (n2.navisionId is null OR n2.navisionId='')AND
s.skladisce_drzava_id=@.idDrzave
GROUP BY n2.navisionId)as T1 )as T2 GROUP BY T2.navisionId
UNION
SELECT DISTINCT
T1. st_nar_dob,nerazdeljena=isnull(kolicina_
dej,kolicina_nar),0 as
razdeljena,
skupaj=isnull(kolicina_dej,kolicina_nar)
from--vse, ki so pod sprosti
(select st_nar_dob from narDobIzdNed WHERE izd_id=@.idIzdelka and (naZalogo
is null OR odobri=1)
UNION --vse, ki so obviseli z navision id-jem in so prav tako pod sprosti
select st_nar_dob=navisionID from skladisceIzdelek WHERE navisionID is not
null AND idSkladisca=4 AND idIzdelka=@.idIzdelka)
as T1 INNER JOIN narDobIzdNed n ON T1.st_nar_dob=n.st_nar_dob AND
n.izd_id=@.idIzdelka
WHERE T1.st_nar_dob not in(SELECT n2.navisionID FROM
(select n1.nar_id,n1.izd_id,max(n1.datum_spremembe) as datumSpremembe FROM
narociloIzdelek n1
WHERE n1.izd_id=@.idIzdelka GROUP BY n1.nar_id,n1.izd_id)AS T1
INNER JOIN narociloIzdelek n2 ON T1.nar_id=n2.nar_id AND T1.izd_id=n2.izd_id
AND T1.datumSpremembe=n2.datum_spremembe WHERE n2.izd_id=@.idIzdelka))as Tk
GROUP BY Tk.st_nar_dob
regards,S
"R.D" <RD@.discussions.microsoft.com> wrote in message
news:726AB188-438B-4DBA-8CE6-DA4E1C35CEC1@.microsoft.com...
> no It should not. check somebody might have updated mean while.
> Post complete script if you find problem
> --
> Regards
> R.D
> --Knowledge gets doubled when shared
>
> "simon" wrote:
>|||-- Check the settings for ansi nulls for the two stored procedures
-- which I believe are set when the stored procedure are created.
-- This could affect your results.
select name, OBJECTPROPERTY ( id , 'ExecIsAnsiNullsOn' ) as
ExecIsAnsiNullsOn
from sysobjects
where OBJECTPROPERTY ( id , 'IsProcedure' )=1
and name in ('test','?')|||thank you, that was the right answer.
In query analyzer is set to ON.
But I don't understand, I don't compare NULL=NULL anywhere in my query.
Does that UNION operator do internally?
I thought that SET ANSI NULLS ON or OFF affects only comparisations.
Regards,
Simon
<markc600@.hotmail.com> wrote in message
news:1128425201.376908.171720@.o13g2000cwo.googlegroups.com...
> -- Check the settings for ansi nulls for the two stored procedures
> -- which I believe are set when the stored procedure are created.
> -- This could affect your results.
> select name, OBJECTPROPERTY ( id , 'ExecIsAnsiNullsOn' ) as
> ExecIsAnsiNullsOn
> from sysobjects
> where OBJECTPROPERTY ( id , 'IsProcedure' )=1
> and name in ('test','?')
>|||On Tue, 4 Oct 2005 14:33:11 +0200, simon wrote:

>thank you, that was the right answer.
>In query analyzer is set to ON.
>But I don't understand, I don't compare NULL=NULL anywhere in my query.
>Does that UNION operator do internally?
>I thought that SET ANSI NULLS ON or OFF affects only comparisations.
Hi Simon,
Your query has many comparisons between columns, such as
WHERE st_nar_dob=t1.navisionId (random snippet)
If both st_nar_dob and t1.navisionId can hold NULL, then there will be
combinations that test as equal with SET ANSI_NULLS OFF, but unequal
with standard ANSI null handling.
As far as I know, both explicit DISTINCT and the implied DISTINCT in the
UNION operator are not affected by ANSI_NULLS setting - but I'm not 100%
sure.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)