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 ?
>
>

Multiple Select Statements in 1 Query

i'm going to guess that there's no way to do this in T-SQL (or any
other SQL implementation), but I figured I'd ask around:
basically, what I'm looking to do is replace this union statement::
SELECT intX
FROM Table1
UNION
SELECT intY
FROM Table1
with a syntactical shortcut:
SELECT intX
SELECT intY
FROM Table1
The need for this shortcut is that my FROM clause is very complex, and
copying it multiple times is messy (and frankly, I'm in no mood to
create a View given the number of non-unique column names in the joined
tables). Anyone have any suggestions?scottstein@.gmail.com wrote:
> i'm going to guess that there's no way to do this in T-SQL (or any
> other SQL implementation), but I figured I'd ask around:
> basically, what I'm looking to do is replace this union statement::
> SELECT intX
> FROM Table1
> UNION
> SELECT intY
> FROM Table1
> with a syntactical shortcut:
> SELECT intX
> SELECT intY
> FROM Table1
> The need for this shortcut is that my FROM clause is very complex, and
> copying it multiple times is messy (and frankly, I'm in no mood to
> create a View given the number of non-unique column names in the joined
> tables). Anyone have any suggestions?
In SQL Server 2005 you can use a CTE. You'll have to assign some unique
column names though:
WITH T1 (intx, inty)
AS
(SELECT ... /* your complex logic here */ )
SELECT intx
FROM T1
UNION
SELECT inty
FROM T1 ;
There may be a better solution by changing the complex part of your
query that you didn't post.
David Portas
SQL Server MVP
--|||if are aren't on 2K5 yet, you can cross join with a sequence table,
like this:
select case when t=1 then expr1 else expr2 end
from(
-- your really complex expression goes here
select 'expr1' expr1, 'expr2' expr2)c
cross join
(select 1 t union all select 2) t
expr1
expr2
(2 row(s) affected)|||Thanks David + Alexander, I appreciate your responses. Unfortunately,
CTE has the same problem as the view -- unique column names. I haven't
even seen 2005 yet, so that isn't an option for now. Again, this
problem isn't unbearable, but it just makes my code a little messy.