Q:

SQL Stored Proc Executing Select before gettting values from other procs

Got a strange problem created a little stored proc which need to execute a couple of other stored procs to get some values before executing the main select statement see below,
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[usp_get_ApplicationUserServiceRoles] 
    @UserId int,
    @ApplicationName varchar(50),
    @ServiceName varchar(50)
AS
BEGIN
    -----------------------------------------   
    SET NOCOUNT ON
    -----------------------------------------
    DECLARE @ApplicationId INT
    exec @ApplicationId = dbo.usp_get_AppIdFromName @ApplicationName
    DECLARE @ServiceId INT
    exec @ServiceId = dbo.usp_get_ServiceIdFromName @ServiceName

    SELECT
    [RoleName]
    FROM 
    [ServiceRoles] s 
    INNER JOIN 
    [ApplicationUserServiceRoles] r
    ON
    s.ServiceRoleId = r.ServiceRoleId
    INNER JOIN
    [ApplicationServices] p
    ON
    s.ServiceId = p.ServiceId
    WHERE
    r.UserId = @UserID
    AND 
    r.ApplicationId = @ApplicationId
    AND
    s.ServiceId = @ServiceId        
END

When I run this stored proc it returns me the two values from the two procs with this proc but not the actual select value.  However when I run the select statement on its own with the values the secondary stored procs return it returns the correct data.
Any idea what's going on, is the select statement running before the two secondary stored procs so the select statement hasn't got the correct values?
Running in SQL 2005

A:

A stored procedure returns a number indicating the execution status of the stored procedure. In order to capture the select statement's output you'll have to use INSERT...EXECUTE (see here for details)
What happens in your case is that each sub-procedure executes but your main procedure fails. Check your output window, it should tell you the error.

A:

The only way to capture the result set of a stored procedure is INSERT ... EXEC:
declare @applicationId int;
declare @tableApplicationId table (ApplicationId ind);
insert into @tableApplicationId
exec dbo.usp_get_AppIdFromName @ApplicationName;
select @applicationId = ApplicationId from @tableApplicationId;

You may want to consider changing dbo.usp_get_AppIdFromName into a function instead, or a procedure that returns @applicationId as OUTPUT parameter.
INSERT ... EXEC has all sort of side effect problems, like nesting issues:

http://www.sommarskog.se/share_data.html#INSERTEXEC
The Hidden Costs of INSERT EXEC 

