direct T-SQL differs from SP - SQL Server Forums

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

SQL Server Development (2000)

direct T-SQL differs from SP

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-10-04 : 09:04:17

Conny writes "Different result sets from stored procedure and
direct SQL calls when using group by and subselects!?!
(Version SQL-Server 7.00.961
on Windows NT 4.0 (1381) client Win2k)

The following behavior is very strange to me!
I build up an stored procedure using the
queryAnalyzer like this:

for example I have a table called ArticleSold with fields
rate, color, title and buyDate

I now want to have a monthly list splitted in light- and
dark color

title....tmonth....sumLight....sumDark
100........1.......1000.34......500.43
100........2.......1250.55......103.00
and so on...

I used this query
-------------------------------------------------------------
declare @XYEAR as int

set @XYEAR = 2001

Select title, month(buyDate) as Tmonth,

  (select sum(rate) from ArticleSold where

    month(buyDate) = month(r.buyDate) and

    year(buyDate) = @XYEAR and

    color in ('WHITE','YELLOW', 'GREEN')) as sumLight,

  (select sum(rate) from ArticleSold where

    month(buyDate) = month(r.buyDate) and

    year(buyDate) = @XYEAR and

    color in ('BLACK','BLUE')) as sumDark

from ArticleSold r where year(buyDate) = @XYEAR

group by title, month(buyDate)

--------------------------------------------------------------
and testing it was fine cause it works!
Creating a stored procedure also
succeeded. But calling this stored procedure with the
correct arguments (parameter) always result in

title....tmonth....sumLight....sumDark
100........1.......NULL........NULL
100........2.......NULL........NULL

what happens?
tell me that you are the guru"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources