feeding results from one SP to anohter

Hi,
I need an SP to get the resultset output of another SP.
ie
create Procedure A
as
begin
select * from Area
end
Now in procedure B, how can I get these results into a cursor form processin
g?
Also, now to through another slant on it, what if Procedure A is in a remote
database, does the same logic apply?
Thanks,
Steve
(similar to a previous post today - sorry)One way to do would be to use global temporary tables (## prefix).
But this is more like a procedural programming approach which is more
suitable for client side as opposed to database side.
Could you please elaborate more on what you are trying to do.
Here is a sample code.
HTH...
use pubs
go
-- ========================================
=====
-- Create procedure basic template
-- ========================================
=====
-- creating the store procedure
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'mytestprocA'
AND type = 'P')
DROP PROCEDURE dbo.mytestprocA
GO
CREATE PROCEDURE dbo.mytestprocA
AS
drop table ##global_temp
select top 5 * into ##global_temp from authors
GO
-- ========================================
=====
-- example to execute the store procedure
-- ========================================
=====
EXECUTE dbo.mytestprocA
GO
use pubs
go
-- ========================================
=====
-- Create procedure basic template
-- ========================================
=====
-- creating the store procedure
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'mytestprocB'
AND type = 'P')
DROP PROCEDURE dbo.mytestprocB
GO
CREATE PROCEDURE dbo.mytestprocB
AS
DECLARE temp_Cursor CURSOR FOR
select * from ##global_temp
OPEN temp_Cursor
FETCH NEXT FROM temp_Cursor
WHILE @.@.FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM temp_Cursor
END
CLOSE temp_Cursor
DEALLOCATE temp_Cursor
GO
-- ========================================
=====
-- example to execute the store procedure
-- ========================================
=====
EXECUTE dbo.mytestprocB
GO
http://zulfiqar.typepad.com
BSEE, MCP
"Steve" wrote:

> Hi,
> I need an SP to get the resultset output of another SP.
> ie
> create Procedure A
> as
> begin
> select * from Area
> end
> Now in procedure B, how can I get these results into a cursor form process
ing?
> Also, now to through another slant on it, what if Procedure A is in a remo
te
> database, does the same logic apply?
> Thanks,
> Steve
> (similar to a previous post today - sorry)
>|||Best way to do this is change proc A to a function.
2nd best:
insert #table
Exec A
I would also ask what you are using the cursor for, but that is only because
cursors are evil :) Seriously most every use for a cursor has a far easier
to manage set based solution, and that is what we are here to help you with.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Steve" <Steve@.discussions.microsoft.com> wrote in message
news:8BA898FD-43B9-401C-83DB-BD314A2EAA19@.microsoft.com...
> Hi,
> I need an SP to get the resultset output of another SP.
> ie
> create Procedure A
> as
> begin
> select * from Area
> end
> Now in procedure B, how can I get these results into a cursor form
> processing?
> Also, now to through another slant on it, what if Procedure A is in a
> remote
> database, does the same logic apply?
> Thanks,
> Steve
> (similar to a previous post today - sorry)
>

Feedback from Tape Backups

Hi Folks,
I'm trying to find a way to get access to the STATS output from the BACKUP
command during automated backups.
When running the BACKUP command in QA it shows the output, line by line, as
the backup progresses. Is there any way I can get this information when the
BACKUP statement is run from a job? I thought the console tool might have
been the answer, but it doesn't show any progress messages.
Is the only option to use the Job/Step/Adv/Output to file option, and "tail"
the file?
TIA
J.
In this case, the client application is SQL Server Agent, more specific, the sub-component in Agent
that executes your TSQL job steps. And Agent isn't written to output this information "as it goes".
It might update the output file as information comes in (I haven't tested this), so I think that
this is probably your best bet. Another option is of course to schedule an EXE file that you wrote
and you have in your EXE file code to output the information to where ever you want it to go.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"J Hunter" <ms-nntp-nospam@.jshunter.co.uk> wrote in message
news:eL976GjHFHA.1860@.TK2MSFTNGP15.phx.gbl...
> Hi Folks,
> I'm trying to find a way to get access to the STATS output from the BACKUP
> command during automated backups.
> When running the BACKUP command in QA it shows the output, line by line, as
> the backup progresses. Is there any way I can get this information when the
> BACKUP statement is run from a job? I thought the console tool might have
> been the answer, but it doesn't show any progress messages.
> Is the only option to use the Job/Step/Adv/Output to file option, and "tail"
> the file?
> TIA
> J.
>
>
|||Thanks Tibor
With regard to your comments on creating my own EXE, how would I go about
getting access to the messages - given that they are not returned as part of
a result set?
Cheers
J.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OU%23Lr5lHFHA.4060@.TK2MSFTNGP14.phx.gbl...
> In this case, the client application is SQL Server Agent, more specific,
the sub-component in Agent
> that executes your TSQL job steps. And Agent isn't written to output this
information "as it goes".
> It might update the output file as information comes in (I haven't tested
this), so I think that
> this is probably your best bet. Another option is of course to schedule an
EXE file that you wrote
> and you have in your EXE file code to output the information to where ever
you want it to go.[vbcol=seagreen]
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "J Hunter" <ms-nntp-nospam@.jshunter.co.uk> wrote in message
> news:eL976GjHFHA.1860@.TK2MSFTNGP15.phx.gbl...
BACKUP[vbcol=seagreen]
as[vbcol=seagreen]
the[vbcol=seagreen]
have[vbcol=seagreen]
"tail"
>
|||First I believe that you have to program asynchronously. Otherwise the command will be blocked until
it has finished. These are returned as messages, technically same way as PRINT and errors. I'm no
API expert, so you might want to investigate this further and possibly asking in a newsgroup
dedicated to the API you are using (ADO, ADO.NET etc).
The bigger problem is how your EXE will output to the screen. Remember that agent is a service (in
"NT") and a service has no Window context...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"J Hunter" <ms-nntp-nospam@.jshunter.co.uk> wrote in message
news:ePdMhboHFHA.236@.TK2MSFTNGP14.phx.gbl...
> Thanks Tibor
> With regard to your comments on creating my own EXE, how would I go about
> getting access to the messages - given that they are not returned as part of
> a result set?
> Cheers
> J.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:OU%23Lr5lHFHA.4060@.TK2MSFTNGP14.phx.gbl...
> the sub-component in Agent
> information "as it goes".
> this), so I think that
> EXE file that you wrote
> you want it to go.
> BACKUP
> as
> the
> have
> "tail"
>
sql