Read print output from within a stored procedure by calling another

Question

Read print output from within a stored procedure by calling another

kinnon_2000

Say Hey Kid

Points: 702
More actions
June 15, 2008 at 3:09 pm

#188005

Hi,
Lets say i have two stored procedures, one callign the other to get its output into a varible. the first procedure has only one string as its output. The second proc fetches this value into a variable.
Currently, i do it as follows:
proc1 returns a table with one field called txt containing 'this is a string of text'
proc2 gets the data with something like INSERT INTO @texttable EXEC proc1 @var1, @var2
then I have to do a SELECT and FETCH etc to get the value out of @texttable and into @sometxt
Let me clarify the way i want:
proc1 does a little querying and returns the message PRINT 'this is a string of text'
proc2 calls this and records the message to @sometxt text variable with something like
SET @sometxt = EXEC proc1 @var1, @var2
Anyone shed some light on this? Is this possible in some way?
Regards,
kinnon

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 1

kinnon_2000 (6/15/2008)
Hi,
Lets say i have two stored procedures, one callign the other to get its output into a varible. the first procedure has only one string as its output. The second proc fetches this value into a variable.
Currently, i do it as follows:
proc1 returns a table with one field called txt containing 'this is a string of text'
proc2 gets the data with something like INSERT INTO @texttable EXEC proc1 @var1, @var2
then I have to do a SELECT and FETCH etc to get the value out of @texttable and into @sometxt
Let me clarify the way i want:
proc1 does a little querying and returns the message PRINT 'this is a string of text'
proc2 calls this and records the message to @sometxt text variable with something like
SET @sometxt = EXEC proc1 @var1, @var2
Anyone shed some light on this? Is this possible in some way?
Regards,
kinnon

No, this is not really possible the way you are looking at it. However, you could use an output parameter instead and it would look like:

DECLARE @sometext varchar(50); -- size this appropriately

EXECUTE proc1 @var1, @var2, @sometext output

In proc1 - you would do the following:

CREATE PROCEDURE dbo.proc1

@var1 datatype

,@var2 datatype

,@sometext varchar(50) output

AS

...

SET @sometext = 'this is some text';

RETURN;

Jeff

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 2

Sure... let's shed some light on this... this sounds like you're processing one row at a time... That's RBAR and that's a mistake. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

kinnon_2000 Say Hey Kid Points: 702 More actions · Answer 3

Hi, great solution, thanks.

yup rbar indeed, but its the only way im aware of to get pagination working with sql server 2000. I do hate cursors, but the data is so scattered, and stored in wierd and wonderful ways. the proc1 above actually pulls together a comma seporated string on descriptive data related the data in proc2 for a web report. I dont know of a sql server query that could concat multiple fields from a table, as a join, into a single field. Can that be done?

because its for a web app, pagination is essential for the display, and keeps the speed up. hopefully once weve upgraded all our servers to 2005 or better i'll be able to make some well needed redesign of the sp's. the database structure aint great, but we cant change it without sourcing the rewrite of some very serious applications. company wouldnt fund it. I guess the saving grace is the load on the db's is fairly light.

Regards,

kinnon

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 4

kinnon_2000 (6/15/2008)
I dont know of a sql server query that could concatinate multiple fields from a tabe, as a join, into a single field. Can that be done?

Absolutely... see the following URL...

http://www.sqlservercentral.com/articles/Test+Data/61572/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

kinnon_2000 Say Hey Kid Points: 702 More actions · Answer 5

hmm, cool. think ill be doing some performance tuning...

thanks again,

kinnon