Hide subquery results in stored procedure

[John]

Hi everyone, I have a stored procedure which I use to query a table.

The first part of the stored procedure uses a cursor to update a temp table
whilst the second part of the query actually retrieves information from a
database table utilising information based on the temp table.

My problem is that when I run the procedure, the cursors status is output
and therefore becomes part of the result set. I really only want the
information returned returned by the second query as my result set.

So, is it possible to hide the results of the cursor so that it does not
become part of the result set? I have tried nocount on around the cursor
routine but this does not work

Thanks in advance
Mark

[Matik]

Hej John,

If I think correctly, you will to get ONLY rows returned by any second
query. The nocount option, is for hidding messages, that SQL server
brings additionaly to the query resultset (like rowcount of selected
rows).
But as far as I know, it want solve this problem that you have.
Unfortunately, I'm fighting with the same problem that you have (if I
understood you properly), so that's why I will be very happy, to here
the possible solution....

Sorry,

Mateusz

[Matik]

Hej John,

If I think correctly, you will to get ONLY rows returned by any second
query. The nocount option, is for hidding messages, that SQL server
brings additionaly to the query resultset (like rowcount of selected
rows).
But as far as I know, it want solve this problem that you have.
Unfortunately, I'm fighting with the same problem that you have (if I
understood you properly), so that's why I will be very happy, to here
the possible solution....

Sorry,

Mateusz

[Hugo Kornelis]

On Tue, 7 Dec 2004 13:24:18 +1300, John wrote:

Hi everyone, I have a stored procedure which I use to query a table.

The first part of the stored procedure uses a cursor to update a temp table
whilst the second part of the query actually retrieves information from a
database table utilising information based on the temp table.

My problem is that when I run the procedure, the cursors status is output
and therefore becomes part of the result set. I really only want the
information returned returned by the second query as my result set.

So, is it possible to hide the results of the cursor so that it does not
become part of the result set? I have tried nocount on around the cursor
routine but this does not work

Thanks in advance
Mark

Hi Mark (and Mateusz),

It's really hard to troubleshoot code you can't see. If you want help,
please post the following:

1. The code of the stored procedure (of course) and of any stored
procedures called from that procedure,
2. The structure of all tables used in the stored procedure (posted as
CREATE TABLE statements, including all constraints and properties, though
irrelevant columns and their constraints may be omitted),
3. Sample data that illustrates both what you are trying to accomplish and
the unwanted behaviour you are seeing (posted as INSERT statements, as I
don't have the time to manually copy your sample data),
4. The output you expect to see from the sample data,
5. A short description of the business problem you are trying to solve.
Try to describe it without assuming that I know anything about your
business, as I probably don't.

Also see http://www.aspfaq.com/5006.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[John]

Hi, thanks for your help.
The initial code it was to randomly select a row from a table by generating
a random number and associating the new number with the primary key id of my
table. I have since done it another way using the NewID() method of MSSQL

Again thanks for your help
Regards

"Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message
news:bb********************************@4ax.com...

On Tue, 7 Dec 2004 13:24:18 +1300, John wrote:

Hi everyone, I have a stored procedure which I use to query a table.

The first part of the stored procedure uses a cursor to update a temp tablewhilst the second part of the query actually retrieves information from a
database table utilising information based on the temp table.

My problem is that when I run the procedure, the cursors status is output
and therefore becomes part of the result set. I really only want the
information returned returned by the second query as my result set.

So, is it possible to hide the results of the cursor so that it does not
become part of the result set? I have tried nocount on around the cursor
routine but this does not work

Thanks in advance
Mark

Hi Mark (and Mateusz),

It's really hard to troubleshoot code you can't see. If you want help,
please post the following:

1. The code of the stored procedure (of course) and of any stored
procedures called from that procedure,
2. The structure of all tables used in the stored procedure (posted as
CREATE TABLE statements, including all constraints and properties, though
irrelevant columns and their constraints may be omitted),
3. Sample data that illustrates both what you are trying to accomplish and
the unwanted behaviour you are seeing (posted as INSERT statements, as I
don't have the time to manually copy your sample data),
4. The output you expect to see from the sample data,
5. A short description of the business problem you are trying to solve.
Try to describe it without assuming that I know anything about your
business, as I probably don't.

Also see http://www.aspfaq.com/5006.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)