Keep a stored procedure from returning rows...

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 2005 Forums

Transact-SQL (2005)

Keep a stored procedure from returning rows...

Author

Topic

shaunco
Starting Member

3 Posts

Posted - 2007-05-06 : 20:19:53

So, I have two stored procedures - defined as such:


CREATE PROCEDURE [dbo].[SPOne]
    @Value bigint
AS
BEGIN
    SET NOCOUNT ON;
    ...
    SELECT * FROM [dbo].[MyTable] WHERE [Col]=@Value;
    RETURN @@ROWCOUNT;
END

CREATE PROCEDURE [dbo].[SPTwo]
    @Value bigint
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @CallParam bigint;
    SET     @CallParam = 23;  -- actually comes from a select
    ...
    DECLARE @SPRet int;
    EXEC @SPRet = [dbo].[SPOne] @Value = @CallParam;

    SELECT TOP @SPRet * FROM [dbo].[MyOtherTable] WHERE [Col]=@Value;
    RETURN @@ROWCOUNT;
END

Ok... so, when I EXEC SPTwo (from C#), the SqlDataReader that is returned starts out pointing at the rows that were returned from the nested stored procedure call to SPOne.

I can reproduce this in SQL Management Studio by using:


DECLARE	@return_value int;
EXEC	@return_value = [dbo].[SPTwo] @Value = 14

which will return two record sets.

So... I know I have the option to create a temporary table to select the results of the nested stored procedure call in to, which I REALLY don't like the idea of... or I can convert SPOne to a scalar function, but in reality SPOne is much more complicated (updates rows inside of a transaction), and transactions don't seem to be allowed in functions.

Do I have any other options? Am I stuck having to move my C# data reader forward (past the other record sets returned by nested calls)?

I really hope I am not stuck, as this means that if I ever change SPTwo, then the C# also must change to move the proper number of record sets forward...

-Shaun

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-06 : 22:17:46

[code]
declare @rows int
select @rows = count(*)
from
(
SELECT TOP @SPRet * FROM [dbo].[MyOtherTable] WHERE [Col]=@Value order by ...
) a
RETURN rows
[/code]

shaunco
Starting Member

3 Posts

Posted - 2007-05-06 : 23:00:07

KH -
Thanks for the response... as I said above, my examples were SUPER simplified for explanation purposes. The actual queries are VERY complicated and MUST be in separate stored procedures.

All I am really looking for is a way to call a stored procedure from inside a stored procedure and IGNORE the recordset that is returned (WITHOUT a temp table). Perhaps there is a way for a stored procedure to reset its output record set??? this way I could call SPTwo, then RESET_RECORDSET_RETURN (would be nice if it was that simple of a name), then move on.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-07 : 03:09:58

For your SUPER-simplified question, khtan gave a SUPER-simplified answer.

Peter Larsson
Helsingborg, Sweden

shaunco
Starting Member

3 Posts

Posted - 2007-05-07 : 04:22:19

Understood :) - I was not trying to be rude in my response, I was just reclarifying what I was looking for.
...
So, is it not possible to have a stored procedure eat specific SELECT statements (e.g. not return them to the caller), without using a temp table?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources