C# MSSQL - number of recordsets

Hi,

I've got a problem which might be a little bit tricky.
I need to find out if selected stored procedure can return more than
one recordset at execution. I know that that might depend on the
parameter values, but it will be perfect if it would be possible just
to count select statements within the procedure code that are actually
return as a recordsets. Following my idea the following procedure
returns 2 recordsets (it isn't i know, but taht will be much easier to
do and that's fine by me so).
Any help will be appreciated.

CREATE PROCEDURE SP_TEST_PROCEDURE
@PARAM1 INT
AS
BEGIN
IF @PARAM1 = 1
BEGIN
SELECT * FROM PUBS..SALES
END
IF @PARAM1 = 2
BEGIN
SELECT * FROM PUBS..JOBS
END
IF @PARAM1 = 3
BEGIN
SELECT * INTO #TEST_TABLE FROM PUBS.JOBS
END
END

Yes, a stored procedure can return multiple resultsets.

For example, using your test code.

CREATE PROCEDURE dbo.SP_TEST_PROCEDURE
( @Param1 INT )
AS
BEGIN
IF ( @Param1 = 1 ) OR ( @Param1 = 4 )
BEGIN
SELECT * FROM PUBS..SALES
END
IF ( @Param1 = 2 ) OR ( @Param1 = 4 )
BEGIN
SELECT * FROM PUBS..JOBS
END
IF ( @Param1 = 3 ) OR ( @Param1 = 4 )
BEGIN
SELECT * INTO #TEST_TABLE FROM PUBS.JOBS
END
END

EXECUTE dbo.SP_TEST_PROCEDURE @Param1 INT = 4



-- 
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience. 
Most experience comes from bad judgment. 
- Anonymous

You can't help someone get up a hill without getting a little closer to the 
top yourself.
- H. Norman Schwarzkopf


<sajberek@gmail.com> wrote in message news:1164837566.847747.152580@16g2000cwy.googlegroups
.com...
> Hi,
> 
> I've got a problem which might be a little bit tricky.
> I need to find out if selected stored procedure can return more than
> one recordset at execution. I know that that might depend on the
> parameter values, but it will be perfect if it would be possible just
> to count select statements within the procedure code that are actually
> return as a recordsets. Following my idea the following procedure
> returns 2 recordsets (it isn't i know, but taht will be much easier to
> do and that's fine by me so).
> Any help will be appreciated.
> 
> CREATE PROCEDURE SP_TEST_PROCEDURE
> @PARAM1 INT
> AS
> BEGIN
>   IF @PARAM1 = 1
>   BEGIN
>      SELECT * FROM PUBS..SALES
>   END
>   IF @PARAM1 = 2
>   BEGIN
>      SELECT * FROM PUBS..JOBS
>   END
>   IF @PARAM1 = 3
>   BEGIN
>      SELECT * INTO #TEST_TABLE FROM PUBS.JOBS
>   END
> END
>

Yes, a stored procedure can return multiple resultsets.

For example, using your test code.

CREATE PROCEDURE dbo.SP_TEST_PROCEDURE
( @Param1 INT )
AS
BEGIN
IF ( @Param1 = 1 ) OR ( @Param1 = 4 )
BEGIN
SELECT * FROM PUBS..SALES
END
IF ( @Param1 = 2 ) OR ( @Param1 = 4 )
BEGIN
SELECT * FROM PUBS..JOBS
END
IF ( @Param1 = 3 ) OR ( @Param1 = 4 )
BEGIN
SELECT * INTO #TEST_TABLE FROM PUBS.JOBS
END
END

EXECUTE dbo.SP_TEST_PROCEDURE @Param1 INT = 4



-- 
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience. 
Most experience comes from bad judgment. 
- Anonymous

You can't help someone get up a hill without getting a little closer to the 
top yourself.
- H. Norman Schwarzkopf


<sajberek@gmail.com> wrote in message news:1164837566.847747.152580@16g2000cwy.googlegroups
.com...
> Hi,
> 
> I've got a problem which might be a little bit tricky.
> I need to find out if selected stored procedure can return more than
> one recordset at execution. I know that that might depend on the
> parameter values, but it will be perfect if it would be possible just
> to count select statements within the procedure code that are actually
> return as a recordsets. Following my idea the following procedure
> returns 2 recordsets (it isn't i know, but taht will be much easier to
> do and that's fine by me so).
> Any help will be appreciated.
> 
> CREATE PROCEDURE SP_TEST_PROCEDURE
> @PARAM1 INT
> AS
> BEGIN
>   IF @PARAM1 = 1
>   BEGIN
>      SELECT * FROM PUBS..SALES
>   END
>   IF @PARAM1 = 2
>   BEGIN
>      SELECT * FROM PUBS..JOBS
>   END
>   IF @PARAM1 = 3
>   BEGIN
>      SELECT * INTO #TEST_TABLE FROM PUBS.JOBS
>   END
> END
>

Yes I know, but how to count how many of them can it return at once?

On 30 Lis, 05:05, "Arnie Rowland" <a...@1568.com>  wrote:
> Yes, a stored procedure can return multiple resultsets.
>
> For example, using your test code.
>
> CREATE PROCEDURE dbo.SP_TEST_PROCEDURE
>    ( @Param1 INT )
> AS
>    BEGIN
>       IF ( @Param1 =3D 1 ) OR ( @Param1 =3D 4 )
>          BEGIN
>             SELECT * FROM PUBS..SALES
>          END
>       IF ( @Param1 =3D 2 ) OR ( @Param1 =3D 4 )
>          BEGIN
>             SELECT * FROM PUBS..JOBS
>          END
>       IF ( @Param1 =3D 3 ) OR ( @Param1 =3D 4 )
>          BEGIN
>             SELECT * INTO #TEST_TABLE FROM PUBS.JOBS
>          END
>    END
>
> EXECUTE dbo.SP_TEST_PROCEDURE @Param1 INT =3D 4
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
>
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> You can't help someone get up a hill without getting a little closer to t=
he top yourself.
> - H. Norman Schwarzkopf
>
>
>
> <sajbe...@gmail.com> wrote in  messagenews:11648375
66.847747.152580@16g200=
0cwy.googlegroups.com... 
> 
> 

Hi there,

Now, obviously I dont know the particulars and there could be a good
reason as to why you need to do this but its seems like quite a messy
solution.

Are you sure there's no other way to do what your attempting to do?

Perhaps you could use C# to build up dynamic SQL and submit it to the
stored procedure. It would be much easier to figure out how many RS
you're getting back if you built the sql in C#. It could be that you
can't actually do it this way though.

If you need a hand submitting dynamic sql to an SPROC then let me know.

My general advice would be to find another way of what you're doing.

Kindest Regards

Simon

If you want to know how many it 'can' return, then it seems that is
something you should know when you write the code.

If you want to know how many it 'did' (not counting empty sets) return, you
could easily accumulate an output parameter after checking the @@ROWCOUNT.

Otherwise, you request just doesn't make sense.

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf


<sajberek@gmail.com> wrote in message
news:1164870186.154846.289960@l39g2000cwd.googlegroups.com...

Yes I know, but how to count how many of them can it return at once?

On 30 Lis, 05:05, "Arnie Rowland" <a...@1568.com>  wrote:[color=darkred
]
> Yes, a stored procedure can return multiple resultsets.
>
> For example, using your test code.
>
> CREATE PROCEDURE dbo.SP_TEST_PROCEDURE
>    ( @Param1 INT )
> AS
>    BEGIN
>       IF ( @Param1 = 1 ) OR ( @Param1 = 4 )
>          BEGIN
>             SELECT * FROM PUBS..SALES
>          END
>       IF ( @Param1 = 2 ) OR ( @Param1 = 4 )
>          BEGIN
>             SELECT * FROM PUBS..JOBS
>          END
>       IF ( @Param1 = 3 ) OR ( @Param1 = 4 )
>          BEGIN
>             SELECT * INTO #TEST_TABLE FROM PUBS.JOBS
>          END
>    END
>
> EXECUTE dbo.SP_TEST_PROCEDURE @Param1 INT = 4
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
>
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> You can't help someone get up a hill without getting a little closer to
> the top yourself.
> - H. Norman Schwarzkopf
>
>
>
> <sajbe...@gmail.com> wrote in
>  messagenews:11648375
66.847747.152580@16g2000cwy.googlegroups.com... 
> 
> 

Unless you can correctly parse T-SQL, you can't just count the number of
SELECT to determine the number of resultsets. Consider the cases with
subqueries and SELECT can be arbitrarily nested within other SELECT's.

Linchi

"sajberek@gmail.com" wrote:

> Hi,
>
> I've got a problem which might be a little bit tricky.
> I need to find out if selected stored procedure can return more than
> one recordset at execution. I know that that might depend on the
> parameter values, but it will be perfect if it would be possible just
> to count select statements within the procedure code that are actually
> return as a recordsets. Following my idea the following procedure
> returns 2 recordsets (it isn't i know, but taht will be much easier to
> do and that's fine by me so).
> Any help will be appreciated.
>
> CREATE PROCEDURE SP_TEST_PROCEDURE
> @PARAM1 INT
> AS
> BEGIN
>    IF @PARAM1 = 1
>    BEGIN
>       SELECT * FROM PUBS..SALES
>    END
>    IF @PARAM1 = 2
>    BEGIN
>       SELECT * FROM PUBS..JOBS
>    END
>    IF @PARAM1 = 3
>    BEGIN
>       SELECT * INTO #TEST_TABLE FROM PUBS.JOBS
>    END
> END
>
>