Multiple select statment inside a stored procedure

Hi there !
Recently i had written a stored procedure which contain multiple select
statment. One follow by anyother, like the following,
declare @.nTemp int
select count(*) AS TempCount from tableA where conditionA
select @.nTemp = TempCount
select count(*) from tableB where condtionB
select count(*) from tableC where conditionC
select count(*) from tableD where conditionD
...
When i run the store procedure, strange result had been return,
the count from tableA give correct result.
the count from tableB give correct result.
but start from there,
the count(*) from tableC return the same result as B
and the count(*) from tableD return same result as B as well.
If i just run the select count(*) statment from B, C, D individually,
3 different result had been return ( as expected the result would not
be the same. ).
It happens only when i move this store procedure from 1 server to
another.
it sounds like some setting of the server causing this problem. Can
someone help please ?Noodle wrote:
> Hi there !
> Recently i had written a stored procedure which contain multiple select
> statment. One follow by anyother, like the following,
> declare @.nTemp int
> select count(*) AS TempCount from tableA where conditionA
> select @.nTemp = TempCount
I am surprized the above does not give you an error. I would
write it like this:
select @.nTemp = count(*) from tableA where conditionA|||when you write a stored procedure with multiple result sets the statements
should be ended with a ;
in a supporting data provider in a program language you can then even access
all result sets ( like the .Net sql client provider ) with .nextresult or a
simular method
regards
Michel Posseth [MCP]
"Sericinus hunter" <serhunt@.flash.net> wrote in message
news:i8lQe.602$yQ1.361@.newssvr17.news.prodigy.com...
> Noodle wrote:
> I am surprized the above does not give you an error. I would
> write it like this:
> select @.nTemp = count(*) from tableA where conditionA|||On 28 Aug 2005 06:37:51 -0700, Noodle wrote:

>Hi there !
>Recently i had written a stored procedure which contain multiple select
>statment. One follow by anyother, like the following,
>declare @.nTemp int
>select count(*) AS TempCount from tableA where conditionA
>select @.nTemp = TempCount
(snip)
Hi Noodle,
Please post the exact code of your procedure, or at least a working
repro that you have actually tested on your system and that you have
witnessed displaying the same behaviour as your real proc.
The code you posted will throw an error on any server.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Ok, it is my mistake, i didn't write out the code. See if you guys
encounter this problem before
DECLARE @.nTemp smallint
SELECT @.nTemp = MAX(M_INDEX) FROM TABLE_A WHERE RECORD_ID =
@.REC_ID
SELECT @.nTemp AS MAXIMUM_INDEX
--First , get total record
SELECT Count(*) AS TOTAL_RECORD FROM TABLE_B WHERE
RECORD_ID = @.REC_ID AND
M_INDEX = @.nTemp
--Second , get total record with error
SELECT Count(*) AS TOTAL_RECORD_WITH_ERROR FROM TABLE_C WHERE
RECORD_ID = @.REC_ID AND
M_INDEX = @.nTemp AND
(ERR_CODE <> NULL OR ERR_CODE <> '')
--Thrid , get total record with conflict
SELECT Count(*) AS TOTAL_RECORD_WITH_INV_CONFLICT FROM TABLE_D
WHERE
RECORD_ID = @.REC_ID AND
M_INDEX = @.nTemp AND
(INV_CONFLICT <> '0' AND INV_CONFLICT <> NULL )
..
The first ,second and third select all return the same value.
But if i just copy out and execute the select statment individually
the value is different.|||Hi Hugo,
I had posted the code in my previous post.
Have u see this kind of problem before ?
It is kinda strange, it just seems like the first 'select count(*)'
never release its buffer, and make the following select count(*) return
back the same value.
I am sure that the data in the server is correct, if i copy out the
select statment, each of them and run it individually on the query
analyzer it returns back 3 different value.
Is there any setting or option in SQL Server need to be set ?
Can any one help ? This problem already drag me a day...
Thanks...
Hugo Kornelis wrote:
> On 28 Aug 2005 06:37:51 -0700, Noodle wrote:
>
> (snip)
> Hi Noodle,
> Please post the exact code of your procedure, or at least a working
> repro that you have actually tested on your system and that you have
> witnessed displaying the same behaviour as your real proc.
> The code you posted will throw an error on any server.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Guys,
Thanks for your help.
Problem solved.
Some developer update the data wrongly.
sorry for all the trouble.|||By the way,
What is the difference between comparing NULL and Char(0)
in SQL server ?|||Noodle,
Is it possible that the Query Analyzer session has a different
setting of ANSI_NULLS than when you run these together?
I suggest you change each occurrence of
<> NULL
to
IS NOT NULL
and see if you get the desired results.
Steve Kass
Drew University
Noodle wrote:

>Hi Hugo,
>I had posted the code in my previous post.
>Have u see this kind of problem before ?
>It is kinda strange, it just seems like the first 'select count(*)'
>never release its buffer, and make the following select count(*) return
>back the same value.
>I am sure that the data in the server is correct, if i copy out the
>select statment, each of them and run it individually on the query
>analyzer it returns back 3 different value.
>Is there any setting or option in SQL Server need to be set ?
>Can any one help ? This problem already drag me a day...
>Thanks...
>
>Hugo Kornelis wrote:
>
>
>|||NULL means "there is no value here". NULL is the absence of a
value, and it is not a value. CHAR(0) is a value, the one-character
string containing the single byte with value 0.
Think of your column as an envelope. If the envelope is empty, you
that column IS NULL. If the envelope contains one byte of information,
and the byte value is zero, you have CHAR(0) in the column. Empty
is different from "contains CHAR(0)"
Steve Kass
Drew University
Noodle wrote:

>By the way,
>What is the difference between comparing NULL and Char(0)
>in SQL server ?
>
>