sql - Calling stored procedure from other stored procedure -

this stored procedure #1:

alter procedure [dbo].[sp1] begin            declare @test varchar(255)     exec @test = dbo.sp2     set nocount on;      select          cms_org.description, @test             cms_org end 

this stored procedure #2:

alter procedure [dbo].[sp2] begin         set nocount on;      select           cms_mas.description  + '' + convert(varchar(50),              cast(cms_org.amount money), 1)                cms_org      inner join           cms_mas = cms_org.guid = cms_mas.guid end 

the problem here not able execute @test in stored procedure #1 calling stored procedure #2. when execute sp1, got null values instead when execute query of sp2 in sp1, got correct value. may know possible solution or similar examples can solve issue?

your stored proc sp2 outputs result of select, stored procs, returns integer using return statement. don't have return statement, sql server generates 1 you: return 0. purpose of return code give feedback on whether ran expected. convention, return code of 0 means no errors.

this shows difference between return code , output of stored proc. create temp table #output capture rows of select stored proc outputs.

declare @return_code int  -- capture output of stored proc sp2 in temp table create table #output( column_data varchar(max) ) insert #output( column_data ) exec @return_code = dbo.sp2   -- returns 0 because have no return statement  -- extract column_data #output variable @test -- if there more 1 row in #output, take last 1 declare @test varchar(255) select @test = column_data #output