Getting value from stored procedure in another stored procedure

Question

Sorry, lots of code coming up..

I saw another question like this that used output parameters. I'm using the RETURN statement to return the value I want to use.

I have one stored procedure InsertMessage that looks like this:

ALTER PROCEDURE dbo.InsertNewMessage

    (
    @messageText text,
    @dateTime DATETIME,
    @byEmail bit,
    @bySMS bit
    )

AS
    DECLARE @NewId int
    BEGIN
    BEGIN TRANSACTION

    INSERT INTO MessageSet VALUES (@byEmail, @bySMS, @dateTime, @messageText)
    SET @NewId = SCOPE_IDENTITY()
    COMMIT
    END

    RETURN @NewId

which another stored procedure uses:

ALTER PROCEDURE dbo.InsertMessageFromUserToGroup

    (
    @userEmail nvarchar(256),
    @groupId int,
    @messageText text,

    @bySMS bit,
    @byEmail bit
    )

AS
    --Inserts a new message to a group
    DECLARE @messageId int
    DECLARE @dateTime DATETIME = GETDATE()
    --First check if user is a part of the group
    IF NOT EXISTS (SELECT userEmail FROM UserToGroupSet WHERE userEmail = @userEmail AND groupId = @groupId)
        RETURN 'User not part of group'
    ELSE --User is a part of the group, add message
    BEGIN
        BEGIN TRANSACTION
            SET @messageId = [dbo].[InsertNewMessage](@messageText, @dateTime, @bySMS, @byEmail)
            INSERT INTO MessageToUser VALUES(@userEmail, @messageId)
            INSERT INTO MessageToGroup VALUES(@messageId, @groupId)
        COMMIT
    END

The row that causes the trouble and of which I'm unsure how to handle is this one:

SET @messageId = [dbo].[InsertNewMessage](@messageText, @dateTime, @bySMS, @byEmail)

The syntax seems ok because I can save it. When I run it I get the error message:

Running [dbo].[InsertMessageFromUserToGroup] ( @userEmail = [email protected], @groupId = 5, @messageText = sdfsdf, @bySMS = false, @byEmail = true ).

Cannot find either column "dbo" or the user-defined function or aggregate "dbo.InsertNewMessage", or the name is ambiguous.
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.
No rows affected.
(0 row(s) returned)
@RETURN_VALUE = 
Finished running [dbo].[InsertMessageFromUserToGroup].

It seems as if the other stored procedure can't be found. I've tried different ways of calling the procedure but everything else fails as well. Any suggestions?

Answer 1

Try changing

SET @messageId = [dbo].[InsertNewMessage](@messageText, @dateTime, @bySMS, 
    @byEmail)

to

EXEC @messageId = [dbo].[InsertNewMessage] @messageText, @dateTime, @bySMS,
     @byEmail

Notice that SET has been changed to EXEC, and the parentheses have been removed from the parameters.

See the example in the MSDN documenation at the end of the article for more information.