Need to get excute multiple stored procedures

Hi Guys,
I am trying to execute a stored procedure within another one and having no luck

The main sp is like so just selecting distinct data
SELECT DISTINCT
                      dbo.IDNT_CSE.IDNT_CSE_ID_NM, dbo.IDNT_CSG.CST_PG_NM, dbo.IDNT_CSG.IDNT_CSE_CRT_DT, dbo.IDNT_CSE.CST_NM,
                      dbo.IDNT_CSE_STAT.META_DAT_VAL_ID_NM
FROM         dbo.IDNT_CSE INNER JOIN
                      dbo.IDNT_CSE_ASGN ON dbo.IDNT_CSE.IDNT_CSG_ID_NM = dbo.IDNT_CSG_ASGN.IDNT_CSE_ID_NM INNER JOIN
                      dbo.IDNT_CSE_STAT ON dbo.IDNT_CSE.IDNT_CSG_ID_NM = dbo.IDNT_CSG_STAT.IDNT_CSE_ID_NM
WHERE     (dbo.IDNT_CSG_ASGN.IDNT_CSG_ID_NM = @RequestedID)

This stored procedure needs to carry the value of dbo.IDNT_CSE_STAT.META_DAT_VAL_ID_NM into a parameter and send it to the following sp
---------------------------------------

ALTER proc [dbo].[ap_s_get_value_text]
      @value_id int,
      @valuetext varchar(200) output
as

begin
            -- get value id text
            SELECT      @valuetext = MDC.META_DAT_CD_DSC_TXT
            FROM      DBO.META_DAT_VAL MDV
                        INNER JOIN DBO.META_DAT_CD MDC ON MDC.META_DAT_CD_ID_NM = MDV.META_DAT_CD_ID_NM
            WHERE      MDV.META_DAT_VAL_ID_NM = @value_id

            return
end
------------------------------------------------------------
any idea how to achieve this?
thanks
Start Free Trial