SQL Stored Proc Executing Select before gettting values from other procs

Question

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

Answer 1

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.

Answer 2

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

Answer 3

If your "sub" stored procedures are just returning a single value, you are best off using output parameters, like so:

.
.
DECLARE @ApplicationId INT
exec dbo.usp_get_AppIdFromName @ApplicationName, @ApplicationId OUTPUT
.
.

And the sub-proceduure should look something like:

CREATE PROCEDURE dbo.usp_get_AppIdFromName
    @ApplicationName  varchar(50)
   ,@ApplicationId    int  OUTPUT

AS
 BEGIN

    --  Adjust as necessary
    SELECT @ApplicationId = ApplicationId
     from MyApplicationTable
     where Name = @ApplicationName

 END
RETURN 0

(Note that, in your original structure,

exec @ApplicationId = dbo.usp_get_AppIdFromName @ApplicationName

@ApplicationId will be assigned the value of the RETURN statement which, in my example, will always be 0. It is best to reserve this value to return the state of that procedure call--that is, did it work or not.)

Answer 4

I'd use two function calls instead. Chances are these stored procs are called eslewhere and if you modify them with output parameters then something else will break.