SP returns different result in Query Analyzer

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SP returns different result in Query Analyzer

Author

Topic

gezdra
Starting Member

5 Posts

Posted - 2005-03-28 : 08:29:36

Hi.
A have an big, complicated stored procedure, which returns some number, eg. 32607.
When I copy its code into SQL Query Analyzer, the same code as it is in that stored procedure, with the same parameters - everything is the same!!! - that code returns a slightly different number, eg. 32606!!!
How is it possible at all?
When I execute it calling the name of sp, result is different than result wich I get when executing its code in Query Analyzer.

In that procedure I have inner subset, and outside that resultset I have
SUM(CASE WHEN field1 = 'xxx' THEN 1 ELSE 0 END),
SUM(CASE WHEN field2 = 'yyy' THEN 1 ELSE 0 END), and so on.

When I delete ouside code, in both cases (calling sp and executing its code) I get the same resultset: that means that I have do not the way to recognize wich row is "guilty" for that difference.
Can anybody help me?

Thanks,
Igor M.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-03-28 : 08:47:38

How are you calling the stored proc outside of query analyzer?

- Jeff

gezdra
Starting Member

5 Posts

Posted - 2005-03-28 : 17:14:36

In principle, from VB app.
But it is not metter how and from where I call it - results are wrong anyway.

Look:
I have
CREATE PROCEDURE mySP
AS
@p1 char(10),
@p2 char(10)
some T-SQL CODE HERE

When, in Query Analyzer (or from VB app, or from Crystal Reports), I write:
exec mySP '01/01/2004', '28/03/2005'
after 'F5', I get 32656.

When, in Query Analyzer, I write
DECLARE p1 char(10), p2 char(10)
SET @p1 = '01/01/2004'
SET @p2 = '28/03/2005'
the very same T-SQL code, as it is in mySP - here
, after 'F5', I get - 32657!!!

The query itself is a little complicated (for me, I'm not a 'big face'), it uses inner subquery, and that subquery RETURNS the same numbers of rows (32657, as it returns when I execute whole code in QA), but outside I have an grouping clause (with SUM(CASE ...)) clauses - it seems that here something is wrong.
But I simply do not understand - how it is possible - the same code - different results?!?

Igor M.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-03-28 : 18:59:08

well, obviously we cannot help you if you don't provide specific code. As you may have learned over the years using computers, when you write code things have to be very exact and precise. Saying "I do something kind of like this" is pretty vague, and makes it especially hard to troubleshoot errors that may be due to rounding or datatype conversions.

By the way, if your procedure accepts dates as parameters, why are they char() datatypes? That small clue leads me to believe that indeed datatype conversions might be causing your problems, since from what small code I've seen it appears that you are using them improperly.

- Jeff

gezdra
Starting Member

5 Posts

Posted - 2005-03-29 : 01:53:04

Hi.
You asked - here it is:

CREATE PROCEDURE mySP
@From as char(10),
@To as char(10),
@Organizations As varchar(800)
AS

SET NOCOUNT ON
DECLARE @dFrom As datetime
DECLARE @dTo As datetime
SET @dFrom = CONVERT(datetime, @From, 103)
SET @dTo = CONVERT(datetime, @To, 103)

SELECT
SUM (CASE StateCause WHEN '1' THEN 1 ELSE 0 END) 'P1',
SUM (CASE StateCause WHEN '2' THEN 1 ELSE 0 END) 'P2',
SUM (CASE StateCause WHEN '71' THEN 1 ELSE 0 END) 'P71',
SUM (CASE StateCause WHEN '72' THEN 1 ELSE 0 END) 'P72',
SUM (CASE StateCause WHEN '164' THEN 1 ELSE 0 END) 'P164',
SUM (CASE StateCause WHEN '165' THEN 1 ELSE 0 END) 'P165',
SUM (CASE StateCause WHEN '4' THEN 1 ELSE 0 END) 'P4',
SUM (CASE StateCause WHEN '6' THEN 1 ELSE 0 END) 'P6',
SUM (CASE StateCause WHEN '9' THEN 1 ELSE 0 END) 'P9',
SUM (CASE StateCause WHEN '10' THEN 1 ELSE 0 END) 'P10',
SUM (CASE StateCause WHEN '13' THEN 1 ELSE 0 END) 'P13',
SUM (CASE StateCause WHEN '16' THEN 1 ELSE 0 END) 'P16',
SUM (CASE StateCause WHEN '167' THEN 1 ELSE 0 END) 'P167',
SUM (CASE StateCause WHEN '169' THEN 1 ELSE 0 END) 'P169',
SUM (CASE WHEN StateCause IN ('3', '5', '8', '11', '12', '14', '15', '17', '166') THEN 1 ELSE 0 END) RestPasive,
SUM (CASE WHEN StateCause IN ('1', '2', '71', '72', '164', '165', '3', '4', '5', '6', '8', '9', '10', '11', '12', '13', '14', '15', '16', '17', '166', '167', '169') THEN 1 ELSE 0 END) TotalPasive

FROM
(
SELECT DISTINCT ManId, StateCause
FROM StateEvidention p WITH (NOLOCK)
WHERE CHARINDEX(SubString(p.OwnerId, 2, 4), @Organizations) > 0 AND
DateDiff(d, @dFrom, p.EvidentionDate) >= 0 AND DateDiff(d, p.EvidentionDate, @dTo) >= 0 AND
/*1. in that period exists row with state <>'1'*/
p.State <> '1' AND
NOT p.StateCause IN ('33', '163', '168', '169') AND
p.EvidentionNo = ( SELECT MAX(EvidentionNo)
FROM StateEvidention WITH (NOLOCK)
WHERE ManId = p.ManId AND
SubString(OwnerId, 2, 4) = SubString(p.OwnerId, 2, 4) AND
DateDiff(d, @dFrom, EvidentionDate) >= 0 AND DateDiff(d, EvidentionDate, @dTo) >= 0 AND
State <> '1' AND
NOT StateCause IN ('33', '163', '168', '169')
) AND
/* 2. State on day before starting period = '1', in that same organization */
(SELECT State
FROM StateEvidention WITH (NOLOCK)
WHERE ManId = p.ManId AND
DateDiff(d, EvidentionDate, @dFrom) > 0 AND
SubString(OwnerId, 2, 4) = SubString(p.OwnerId, 2, 4) AND
EvidentionNo = (SELECT MAX(EvidentionNo)
FROM StateEvidention WITH (NOLOCK)
WHERE ManId = p.ManId AND
DateDiff(d, EvidentionDate, @dFrom) > 0))
= '1' AND
/* 3. State on ending date <> '1' in that organization, or he not exists in that organization at the ending date */
(
(SELECT State
FROM StateEvidention WITH (NOLOCK)
WHERE ManId = p.ManId AND
DateDiff(d, EvidentionDate, @dTo) >= 0 AND
SubString(OwnerId, 2, 4) = SubString(p.OwnerId, 2, 4) AND
EvidentionNo = (SELECT MAX(EvidentionNo)
FROM StateEvidention WITH (NOLOCK)
WHERE ManId = p.ManId AND
DateDiff(d, EvidentionDate, @dTo) >= 0))
<> '1'
OR
(SELECT ManId
FROM StateEvidention WITH (NOLOCK)
WHERE ManId = p.ManId AND
SubString(OwnerId, 2, 4) = SubString(p.OwnerId, 2, 4) AND
EvidentionNo = (SELECT MAX(EvidentionNo)
FROM StateEvidention WITH (NOLOCK)
WHERE ManId = p.ManId AND
DateDiff(d, EvidentionDate, @dTo) >= 0)) IS NULL
)
UNION
/*
Man has mooved from one organization to another:
*/
SELECT DISTINCT p.ManId, '169' AS StateCause
FROM StateEvidention p WITH (NOLOCK)
WHERE CHARINDEX(SubString(p.OwnerId, 2, 4), @Organizations) > 0 AND
/* 1. exists row where man move from one organization to another */
p.State = '1' AND
(SELECT State
FROM StateEvidention WITH (NOLOCK)
WHERE ManId = p.ManId and
DateDiff(d, @dFrom, EvidentionDate) >= 0 AND DateDiff(d, EvidentionDate, @dTo) >= 0 AND
SubString(OwnerId, 2, 4) <> SubString(p.OwnerId, 2, 4) AND
EvidentionNo = p.EvidentionNo + 1) = '1' AND
/*2. state on the day before starting date = '1' in that organization */
(SELECT State
FROM StateEvidention WITH (NOLOCK)
WHERE ManId = p.ManId AND
DateDiff(d, EvidentionDate, @dFrom) > 0 AND
SubString(OwnerId, 2, 4) = SubString(p.OwnerId, 2, 4) AND
EvidentionNo = (SELECT MAX(EvidentionNo)
FROM StateEvidention WITH (NOLOCK)
WHERE ManId = p.ManId AND
DateDiff(d, EvidentionDate, @dFrom) > 0))
= '1' AND
/*3. at the end of period there is no that man in that organization, or it's state in that organization is <> '1'*/
(
(SELECT ManId
FROM StateEvidention WITH (NOLOCK)
WHERE ManId = p.ManId AND
SubString(OwnerId, 2, 4) = SubString(p.OwnerId, 2, 4) AND
EvidentionNo = (SELECT MAX(EvidentionNo)
FROM StateEvidention WITH (NOLOCK)
WHERE ManId = p.ManId AND
DateDiff(d, EvidentionDate, @dTo) >= 0)) IS NULL
OR
(SELECT State
FROM StateEvidention
WHERE ManId = p.ManId AND
DateDiff(d, EvidentionDate, @dTo) >= 0 AND
SubString(OwnerId, 2, 4) = SubString(p.OwnerId, 2, 4) AND
EvidentionNo = (SELECT MAX(EvidentionNo)
FROM StateEvidention
WHERE ManId = p.ManId AND
DateDiff(d, EvidentionDate, @dTo) >= 0))
<> '1'
)
) g

GO

Excuse me...

mr_mist
Grunnio

1870 Posts

Posted - 2005-03-29 : 08:22:18

My guess would be that this is probably down to the different ways in which the applications are treating your dates, and the boundries thereof. For example, you are passing through a date value that the application might be interpreting as meaning midday on that date, when if you were to run it in query analyzer then it would interpret it as being midnight that day. So that's one possibility maybe. Try adding times to your date values to see if that fixes anything.

-------
Moo. :)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-03-29 : 09:00:30

sorry, I should have been more clear: we need to the see the code for the two ways you are calling the stored proc, since you are saying that the two methods you are trying return different results.

Hopefully you don't need help tweaking with that SQL code -- it's pretty ugly and very inefficient (DISTINCT's everywhere, CHARINDEX joins, WITH(NOLOCK) hints, criteria that cannot use indexes, long lists of data values hard-coded, etc).

And, again, your procedure should accept datetime datatypes as the parameters, NOT char()'s, if dates are what you want to have passed in.

- Jeff

gezdra
Starting Member

5 Posts

Posted - 2005-03-30 : 04:53:22

Hi!
And thank you to all of you!

I have just find out what cause my problem...
It's NOT the date problem.
It's NULL value and NOT IN clause problem.

NOT IN clause works differently in (my) Query Analyzer, than in (my) stored procedures.
Solution: SET ANSI_NULLS OFF clause should be set in QueryAnalyzer code.

Everybody can try it.
Look at this example.
1. Make a new table, Table1.
2. Add two fileds:
f1, varchar(50), Allow nulls
f2, varchar(50), Allow nulls
3. Add three rows:
f1 = 'a', f2 = 'a'
f1 = 'b', f2 = 'b'
f1 = 'c', f2 = NULL (do not insert any value).
4. Start Query Analyzer, put this code in it and start it (F5):
SELECT * FROM Table1 WHERE NOT f2 IN ('b', 'c', 'd')
You will receive, as a resultset, JUST ONE row:
f1='a' f2='a'
(e.g. there is no the third row, where f2 is Null)
5. Now make stored procedure 'mySP' and put THE VERY SAME CODE IN IT:
CREATE PROCEDURE mySP
AS
SELECT * FROM Table1 WHERE NOT f2 IN ('b', 'c', 'd')
6. Run that stored procedure from anywhere you want - for example, from the same Query Analyzer:
exec mySP
You will receive TWO rows as a resultset:
f1='a' f2='a'
f1='c' f2=NULL
----------------------------------
Solution:
When this code is executed in Query Analyzer:
SET ANSI_NULLS OFF
SELECT * FROM Table1 WHERE NOT f2 IN ('b', 'c', 'd')
, it wil return two rows:
f1='a' f2='a'
f1='c' f2=NULL

Sorry for bothering you!

Igor

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources