Hello
I have te following problem:
I have made a cube with quotations. But a quotation consist of one or more
lines. On each line a product is selected (with price, amount, etc.).
The records in my fact table are quotationlines (not quotation-headers),
because the producthierarchy is linked to the articles (in quotationlines).
I would like to count the quotations (not the lines).
The lines linked to a certain quotation have the same document number.
How can I count the number of quotations, e.g. by using the function
calculated member or...? I have tried something but without a good result.
Can you help me?
Thanks in advance
HansIs the relatoinship between the document number and quotation is one to one?
If then, it's possible to aggregate the document number with aggregation
function of "distinct count". You can set the property of measure in Cube
Editor.
Ohjoo Kwon
"Hans" <Hans@.discussions.microsoft.com> wrote in message
news:C28A061E-1975-47A9-8DE2-7489FDED0C3B@.microsoft.com...
> Hello
> I have te following problem:
> I have made a cube with quotations. But a quotation consist of one or more
> lines. On each line a product is selected (with price, amount, etc.).
> The records in my fact table are quotationlines (not quotation-headers),
> because the producthierarchy is linked to the articles (in
quotationlines).
> I would like to count the quotations (not the lines).
> The lines linked to a certain quotation have the same document number.
> How can I count the number of quotations, e.g. by using the function
> calculated member or...? I have tried something but without a good result.
> Can you help me?
> Thanks in advance
> Hans|||Thank you,
But I still have a problem. At the Aggregate Function the option 'count
distinct only appears when the source field is an Numeric field type. The
documentnumber I would like to count (distinct count) is a Text-field (e.g.
VF05001234).
Do you have any ideas to solve this problem?
Or is the problem not the field type, but something else I don't know?
Hans
"Ohjoo Kwon" wrote:
> Is the relatoinship between the document number and quotation is one to on
e?
> If then, it's possible to aggregate the document number with aggregation
> function of "distinct count". You can set the property of measure in Cube
> Editor.
> Ohjoo Kwon
>
> "Hans" <Hans@.discussions.microsoft.com> wrote in message
> news:C28A061E-1975-47A9-8DE2-7489FDED0C3B@.microsoft.com...
> quotationlines).
>
>|||Yes, you are right. It must be numeric.
I recommend you use surrogate key mapped to document number.
Ohjoo Kwon
"hans.de.korte@.verder.nl"
<hans.de.korte@.verder.nl@.discussions.microsoft.com> wrote in message
news:BB9AA35C-6772-4026-B656-BCC31FF21533@.microsoft.com...
> Thank you,
> But I still have a problem. At the Aggregate Function the option 'count
> distinct only appears when the source field is an Numeric field type. The
> documentnumber I would like to count (distinct count) is a Text-field
(e.g.[vbcol=seagreen]
> VF05001234).
> Do you have any ideas to solve this problem?
> Or is the problem not the field type, but something else I don't know?
> Hans
> "Ohjoo Kwon" wrote:
>
one?[vbcol=seagreen]
Cube[vbcol=seagreen]
more[vbcol=seagreen]
quotation-headers),[vbcol=seagreen]
result.[vbcol=seagreen]|||Thank you!!
"Ohjoo Kwon" wrote:
> Yes, you are right. It must be numeric.
> I recommend you use surrogate key mapped to document number.
> Ohjoo Kwon
> "hans.de.korte@.verder.nl"
> <hans.de.korte@.verder.nl@.discussions.microsoft.com> wrote in message
> news:BB9AA35C-6772-4026-B656-BCC31FF21533@.microsoft.com...
> (e.g.
> one?
> Cube
> more
> quotation-headers),
> result.
>
>
Showing posts with label selected. Show all posts
Showing posts with label selected. Show all posts
Tuesday, March 27, 2012
Friday, February 24, 2012
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.googlegro ups.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.googlegro ups.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:[vbcol=seagreen]
> 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:1164837566.847747.152580@.16g2000cwy.go oglegroups.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.googlegr oups.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:[vbcol=seagreen]
> 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:1164837566.847747.152580@.16g2000cwy.go oglegroups.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
>
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.googlegro ups.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.googlegro ups.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:[vbcol=seagreen]
> 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:1164837566.847747.152580@.16g2000cwy.go oglegroups.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.googlegr oups.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:[vbcol=seagreen]
> 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:1164837566.847747.152580@.16g2000cwy.go oglegroups.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
>
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
ENDThis is a multi-part message in MIME format.
--=_NextPart_000_0297_01C713F1.B92E3DD0
Content-Type: text/plain;
charset="iso-8859-2"
Content-Transfer-Encoding: quoted-printable
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 =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 =3D 1
> BEGIN
> SELECT * FROM PUBS..SALES
> END
> IF @.PARAM1 =3D 2
> BEGIN
> SELECT * FROM PUBS..JOBS
> END
> IF @.PARAM1 =3D 3
> BEGIN
> SELECT * INTO #TEST_TABLE FROM PUBS.JOBS
> END
> END
>
--=_NextPart_000_0297_01C713F1.B92E3DD0
Content-Type: text/html;
charset="iso-8859-2"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
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 &nbs=p; SELECT * FROM =PUBS..SALES END IF ( @.Param1 =3D 2 ) OR ( @.Param1 ==3D 4 ) BEGIN &nbs=p; SELECT * FROM =PUBS..JOBS END IF ( @.Param1 =3D 3 ) OR ( @.Param1 ==3D 4 ) BEGIN &nbs=p; 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 the top yourself.- H. Norman Schwarzkopf
=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 =3D 1> BEGIN> SELECT * FROM PUBS..SALES> END> IF @.PARAM1 =3D 2> BEGIN> SELECT =* FROM PUBS..JOBS> END> IF @.PARAM1 =3D 3> BEGIN> SELECT =* INTO #TEST_TABLE FROM PUBS.JOBS> END> END>
--=_NextPart_000_0297_01C713F1.B92E3DD0--|||This is a multi-part message in MIME format.
--=_NextPart_000_0297_01C713F1.B92E3DD0
Content-Type: text/plain;
charset="iso-8859-2"
Content-Transfer-Encoding: quoted-printable
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 =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 =3D 1
> BEGIN
> SELECT * FROM PUBS..SALES
> END
> IF @.PARAM1 =3D 2
> BEGIN
> SELECT * FROM PUBS..JOBS
> END
> IF @.PARAM1 =3D 3
> BEGIN
> SELECT * INTO #TEST_TABLE FROM PUBS.JOBS
> END
> END
>
--=_NextPart_000_0297_01C713F1.B92E3DD0
Content-Type: text/html;
charset="iso-8859-2"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
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 &nbs=p; SELECT * FROM =PUBS..SALES END IF ( @.Param1 =3D 2 ) OR ( @.Param1 ==3D 4 ) BEGIN &nbs=p; SELECT * FROM =PUBS..JOBS END IF ( @.Param1 =3D 3 ) OR ( @.Param1 ==3D 4 ) BEGIN &nbs=p; 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 the top yourself.- H. Norman Schwarzkopf
=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 =3D 1> BEGIN> SELECT * FROM PUBS..SALES> END> IF @.PARAM1 =3D 2> BEGIN> SELECT =* FROM PUBS..JOBS> END> IF @.PARAM1 =3D 3> BEGIN> SELECT =* INTO #TEST_TABLE FROM PUBS.JOBS> END> END>
--=_NextPart_000_0297_01C713F1.B92E3DD0--|||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:1164837566.847747.152580@.16g200=0cwy.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 =3D 1
> > BEGIN
> > SELECT * FROM PUBS..SALES
> > END
> > IF @.PARAM1 =3D 2
> > BEGIN
> > SELECT * FROM PUBS..JOBS
> > END
> > IF @.PARAM1 =3D 3
> > BEGIN
> > SELECT * INTO #TEST_TABLE FROM PUBS.JOBS
> > END
> > END- Ukryj cytowany tekst -- Poka=BF cytowany tekst -|||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:
> 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: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- Ukryj cytowany tekst -- Poka¿ cytowany tekst -
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
ENDThis is a multi-part message in MIME format.
--=_NextPart_000_0297_01C713F1.B92E3DD0
Content-Type: text/plain;
charset="iso-8859-2"
Content-Transfer-Encoding: quoted-printable
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 =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 =3D 1
> BEGIN
> SELECT * FROM PUBS..SALES
> END
> IF @.PARAM1 =3D 2
> BEGIN
> SELECT * FROM PUBS..JOBS
> END
> IF @.PARAM1 =3D 3
> BEGIN
> SELECT * INTO #TEST_TABLE FROM PUBS.JOBS
> END
> END
>
--=_NextPart_000_0297_01C713F1.B92E3DD0
Content-Type: text/html;
charset="iso-8859-2"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
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 &nbs=p; SELECT * FROM =PUBS..SALES END IF ( @.Param1 =3D 2 ) OR ( @.Param1 ==3D 4 ) BEGIN &nbs=p; SELECT * FROM =PUBS..JOBS END IF ( @.Param1 =3D 3 ) OR ( @.Param1 ==3D 4 ) BEGIN &nbs=p; 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 the top yourself.- H. Norman Schwarzkopf
--=_NextPart_000_0297_01C713F1.B92E3DD0--|||This is a multi-part message in MIME format.
--=_NextPart_000_0297_01C713F1.B92E3DD0
Content-Type: text/plain;
charset="iso-8859-2"
Content-Transfer-Encoding: quoted-printable
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 =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 =3D 1
> BEGIN
> SELECT * FROM PUBS..SALES
> END
> IF @.PARAM1 =3D 2
> BEGIN
> SELECT * FROM PUBS..JOBS
> END
> IF @.PARAM1 =3D 3
> BEGIN
> SELECT * INTO #TEST_TABLE FROM PUBS.JOBS
> END
> END
>
--=_NextPart_000_0297_01C713F1.B92E3DD0
Content-Type: text/html;
charset="iso-8859-2"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
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 &nbs=p; SELECT * FROM =PUBS..SALES END IF ( @.Param1 =3D 2 ) OR ( @.Param1 ==3D 4 ) BEGIN &nbs=p; SELECT * FROM =PUBS..JOBS END IF ( @.Param1 =3D 3 ) OR ( @.Param1 ==3D 4 ) BEGIN &nbs=p; 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 the top yourself.- H. Norman Schwarzkopf
--=_NextPart_000_0297_01C713F1.B92E3DD0--|||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:1164837566.847747.152580@.16g200=0cwy.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 =3D 1
> > BEGIN
> > SELECT * FROM PUBS..SALES
> > END
> > IF @.PARAM1 =3D 2
> > BEGIN
> > SELECT * FROM PUBS..JOBS
> > END
> > IF @.PARAM1 =3D 3
> > BEGIN
> > SELECT * INTO #TEST_TABLE FROM PUBS.JOBS
> > END
> > END- Ukryj cytowany tekst -- Poka=BF cytowany tekst -|||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:
> 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: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- Ukryj cytowany tekst -- Poka¿ cytowany tekst -
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
ENDYes, 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:1164837566.847747.152580@.16g200=
0cwy.googlegroups.com...[vbcol=seagreen]
>
>|||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:[vbcol=seagreen]
> 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:1164837566.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
>
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
ENDYes, 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:1164837566.847747.152580@.16g200=
0cwy.googlegroups.com...[vbcol=seagreen]
>
>|||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:[vbcol=seagreen]
> 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:1164837566.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
>
Subscribe to:
Posts (Atom)