SP to fetch only one result set

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

General SQL Server Forums

New to SQL Server Programming

SP to fetch only one result set

Author

Topic

naveengopinathasari
Yak Posting Veteran

60 Posts

Posted - 2010-06-23 : 02:21:07

This is SP
CREATE Procedure TE
AS
BEGIN
Select 'FirstQueryResult';
Select 'SecondQueryResult';
END

I cannot change the SP.
But for some scenarios i want to fetch the 2 result set.
For some scenarios i want to fetch only one result set.

is there any way i can specify while calling my SP i can mention to display the first resultset or the second result set

i should be able to do only at the time of call the sp.

NOTE that i cannot modify this sp, since it is been used in the existing application

Any Help appreciated.

Lets unLearn

Sachin.Nand

2937 Posts

Posted - 2010-06-23 : 02:24:24

If your front end .net?
If it is then you can put the resultset into an dataset & get the second table from the dataset.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

naveengopinathasari
Yak Posting Veteran

60 Posts

Posted - 2010-06-23 : 02:40:43

I dont have any front end, its with the db calls.

Lets unLearn

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-06-23 : 02:50:33

Without altering the SP nothing can do.Unfortunately you didn't have front end too..

You must alter the SP with a variable and a if statement.

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Devart
Posting Yak Master

102 Posts

Posted - 2010-06-23 : 10:32:10

You can try it:

ALTER Procedure TE
(@ID int=null)
AS
BEGIN
if @ID IS NULL
BEGIN
Select 'FirstQueryResult';
Select 'SecondQueryResult';
END
ELSE
BEGIN
IF @ID=1 /*for example*/
SELECT 'FirstQueryResult';
IF @ID=2 /*for example*/
Select 'SecondQueryResult';
END
END
=======================================

exec TE /*you called old script. It call didn't change*/
exec TE 1 or exec TE 2 /*you called a new script*/

Devart,
Tools for SQL Server
http://www.devart.com/dbforge/sql

Sachin.Nand

2937 Posts

Posted - 2010-06-23 : 12:59:45

quote:
Originally posted by Devart

You can try it:

ALTER Procedure TE
(@ID int=null)
AS
BEGIN
if @ID IS NULL
BEGIN
Select 'FirstQueryResult';
Select 'SecondQueryResult';
END
ELSE
BEGIN
IF @ID=1 /*for example*/
SELECT 'FirstQueryResult';
IF @ID=2 /*for example*/
Select 'SecondQueryResult';
END
END
=======================================

exec TE /*you called old script. It call didn't change*/
exec TE 1 or exec TE 2 /*you called a new script*/

Devart,
Tools for SQL Server
http://www.devart.com/dbforge/sql

The OP specified at the first that he cannot ALTER the SP.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

X002548
Not Just a Number

15586 Posts

Posted - 2010-06-23 : 13:22:24

True...but's the problem why you can't use 2 result sets in your code?

You don't know how? or??

How about create a new sproc?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

naveengopinathasari
Yak Posting Veteran

60 Posts

Posted - 2010-06-24 : 03:17:04

Hi All,

None of the above solution are not feasible.
and its not advisable to change the SP.

let me know if any more solution available.

Lets unLearn

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-24 : 03:59:40

quote:
Originally posted by naveengopinathasari

Hi All,

None of the above solution are not feasible.
and its not advisable to change the SP.

let me know if any more solution available.

Lets unLearn

There is no way

Madhivanan

Failing to plan is Planning to fail

X002548
Not Just a Number

15586 Posts

Posted - 2010-06-24 : 14:26:16

quote:
Originally posted by naveengopinathasari

Hi All,

None of the above solution are not feasible.
and its not advisable to change the SP.

let me know if any more solution available.

Lets unLearn

None? Like you can't create a new query as a clone of the other one?

Why Not?

Also, why can't you learn to accept 2 (or more) result sets?

Can you answer those for us?

If not bucko, you're on your own.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-06-24 : 16:30:06

Just a guess but perhaps the unchangable stored proc does something like:

CREATE PROCEDURE SetTest
AS
BEGIN

	SELECT 1
	
	SELECT 2
	
	SELECT 3

END

Now, from inside another stored procedure how do you select only the First or Second data set? I don't know of any way.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-25 : 02:29:55

quote:
Originally posted by Lamprey

Just a guess but perhaps the unchangable stored proc does something like:
CREATE PROCEDURE SetTest
AS
BEGIN

SELECT 1

SELECT 2

SELECT 3

END
Now, from inside another stored procedure how do you select only the First or Second data set? I don't know of any way.

As number of columns returned from each result is same, you can do

create table #t(num int)

insert into #t(num)
EXEC SetTest

But there is no easy way of uniquely identifying the resultset

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources