| Author |
 Topic  |
|
|
heze
Posting Yak Master
USA
192 Posts |
 Posted - 11/07/2006 : 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
Sweden
29138 Posts |
Posted - 11/07/2006 : 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
USA
192 Posts |
Posted - 11/07/2006 : 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
USA
192 Posts |
Posted - 11/20/2006 : 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 |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
heze
Posting Yak Master
USA
192 Posts |
Posted - 11/20/2006 : 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... |
|
|
| |
Topic |
|
intercepting result set from stored procedure
