intercepting result set from stored procedure

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

SQL Server Development (2000)

intercepting result set from stored procedure

Author

Topic

heze
Posting Yak Master

192 Posts

Posted - 2006-11-07 : 11:49:04

Hi,

Im trying to reuse preexisting stored procedures, done by somebody else, ie I can not modify them, But I want to use their output.
How can I do this without having to go to the front end level?, is there any way to redirect their output so that I can use it directly from sql server?

According to what I read this is possible in the new version of SQL server as I understand it has a close interaction with .NET's CLR,
but I have SQL Server 2000

When one queries through "Query Analyzer", one can decide where is the output going to be directed (eg to a file),
how can I do this redirection programatically?

thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-07 : 11:53:23

Yes. Create a temporary table to hold the output from the SP.

INSERT #TempTable
EXEC mySP 'a', 'b', 1

Then you can use the result from the SP which is now stored in the temp table.

Peter Larsson
Helsingborg, Sweden

heze
Posting Yak Master

192 Posts

Posted - 2006-11-07 : 12:01:54

thanks Peso,

So I guess I need to know in advance the schema of the table and do a create table #TempTable statement previous to the exec.

Also, in the case the procedure returns more than 1 result set, how can I retreive them

heze
Posting Yak Master

192 Posts

Posted - 2006-11-20 : 14:03:04

hi Im tryoing to retreive two different resultsets from a stored proc and place them in a temp table but im only being able to get the first result set produced by the sproc, my code is the following
--------------
drop proc GEN_CalculatePercentile
go
create proc GEN_CalculatePercentile
as
select term from Reports.dbo.Terms
select termcode from Reports.dbo.Terms
go

drop table #t
create table #t(
term varchar(20)
)
--
drop table #t2
create table #t2(
term varchar(20)
)

insert into #t
exec GEN_CalculatePercentile

insert into #t2
exec GEN_CalculatePercentile

select * from #t
select * from #t2

---------

what can I do to retreive the second and possible subsequent result sets?

thanks

X002548
Not Just a Number

15586 Posts

Posted - 2006-11-20 : 14:19:41

Sorry it doesn't work that way. I don't think you can do what you want. I may be wrong, but I don't think so.

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

X002548
Not Just a Number

15586 Posts

Posted - 2006-11-20 : 14:20:12

Can't you clone the sproc and split in 2?

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

heze
Posting Yak Master

192 Posts

Posted - 2006-11-20 : 17:08:43

Yes X, will have to do that thnks
I hope they purchase the new version of sql server, of what I read it will be simple to interface with the .net environment, so we can do these kind of things, It would be fine to call a multiple result set sproc within a .net environent from sql server to these kind of things...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources