SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 intercepting result set from stored procedure
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

heze
Posting Yak Master

USA
192 Posts

Posted - 11/07/2006 :  11:49:04  Show Profile  Reply with Quote
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

Sweden
30218 Posts

Posted - 11/07/2006 :  11:53:23  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

heze
Posting Yak Master

USA
192 Posts

Posted - 11/07/2006 :  12:01:54  Show Profile  Reply with Quote
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
Go to Top of Page

heze
Posting Yak Master

USA
192 Posts

Posted - 11/20/2006 :  14:03:04  Show Profile  Reply with Quote
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
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 11/20/2006 :  14:19:41  Show Profile  Reply with Quote
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



Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 11/20/2006 :  14:20:12  Show Profile  Reply with Quote
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



Go to Top of Page

heze
Posting Yak Master

USA
192 Posts

Posted - 11/20/2006 :  17:08:43  Show Profile  Reply with Quote
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...
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000