executing a stored proc from inside another one

[belovedcej]

I have a stored proc which is calling another stored proc. Here's a shortened version:

ALTER Procedure dbo.CSF_UPDATE_Address_SP
(
	@Address_ID Integer,
	@Line_1_VC30 Varchar(60),
	@ReturnID Int OUTPUT)
AS

exec sco_db.dbo.address_update_sp
        @address_id,
	@Line_1_VC30,
	@ReturnID OUTPUT

Select @returnID

the sp I am executing is updates a table and returns the id of the record updated.

No syntax errors, but it won't run, so I assume I'm doing something wrong. Never tried this before, and I suspect it's a problem with my output parameters. It not only won't return the output, but it won't update the table.

 

[bborissov]

To make it more readable:

ALTER Procedure dbo.CSF_UPDATE_Address_SP
(
    @Address_ID Integer,
    @Line_1_VC30 Varchar(60),
    @ReturnID1 Int OUTPUT)
AS

exec sco_db.dbo.address_update_sp
     @address_id,
     @Line_1_VC30,
     @ReturnID2 = @ReturnID1 OUTPUT

Select @ReturnID1

and call it:

DECLARE @SomeAddId int 
DECLARE @Line_VC30  varchar(60)
DECLARE  @ReturnID3  int 
SET @SomeAddId = 1
SET @Line_VC30   = 'blah, blah'
SET @ReturnID3 = 0
EXEC dbo.CSF_UPDATE_Address_SP @SomeAddRessId = @SomeAddId, 
                               @Line_1_VC30   = @Line_VC30,
                               @ReturnID1     = @ReturnID3 OUTPUT

SELECT @ReturnID3

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[belovedcej]

Thanks - that works now in the query analyzer. I just have the problem of getting it to work with my front end. I'll go ask that in the appropriate forum

I appreciate it!