return value from mult stored procedures

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)

return value from mult stored procedures

Author

Topic

Hommer
Aged Yak Warrior

808 Posts

Posted - 2006-10-09 : 13:27:19

Hi, Experts,

I need your help on retrieving value from TWO stored procedures, in my_sp1 calling my_sp2 fassion.

Here is my_sp1

CREATE PROCEDURE [dbo].[my_sp1]
@p1 as varchar(25), @ChgNotes varchar(2000) Output
AS
set nocount on
declare @SP2ChgNotes as varchar(2000)
--extnotes
SELECT head_order_nbr, replace(convert(varchar(8000),head_ext_notes), '>>>','') FROM order_header
WHERE head_order_nbr =@p1
--get additonal charge note
Exec my_sp2 @p1, @SP2ChgNotes Output
SET @ChgNotes = @SP2ChgNotes
Go

CREATE PROCEDURE [dbo].[my_sp2]
@p1 as varchar(25), @Text as varchar(2000), @OutText varchar(2000) Output
AS
set nocount on
--do some checking
SELECT @Text = notes FROM order_header_detail
WHERE head_order_nbr =@p1
--do other processing
Select @OutText = @Text
Go

When debuging them in SQL QA with next syntax, if I call my_sp2 directly, I got the string value, and if I call my_sp1, I got everything else from it, except the string value from my_sp2. What did I miss? Thanks!

declare @mytext as varchar(2000)
exec my_sp1 '349576', @mytext output
print @mytext

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-09 : 13:42:40

my_sp2 is expecting 3 parameters and returning the value via the 3rd parameter.

Whereas when my_sp1 calls my_sp2, it is passing in only 2 parameter

Looks like there is a redundant parameter in my_sp2


CREATE PROCEDURE [dbo].[my_sp2]
@p1 as varchar(25), @Text as varchar(2000) Output , @OutText varchar(2000) Output
AS
set nocount on
--do some checking
SELECT @Text = notes FROM order_header_detail
WHERE head_order_nbr =@p1
--do other processing
Select @OutText = @Text
Go

Hommer
Aged Yak Warrior

808 Posts

Posted - 2006-10-09 : 13:49:55

my bad. The @Text in my_sp2 should be decleared as follow, and is a internal variable in the real version.

CREATE PROCEDURE [dbo].[my_sp2]
@p1 as varchar(25), @OutText varchar(2000) Output
AS
set nocount on
declear @Text as varchar(2000)
--do some checking
SELECT @Text = notes FROM order_header_detail
WHERE head_order_nbr =@p1
--do other processing
Select @OutText = @Text
Go

Hommer
Aged Yak Warrior

808 Posts

Posted - 2006-10-09 : 14:55:28

Ok, I got the return text. It just that it did not show up on QA Grids tab with other data. The print @ sent it to the message tab.

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources