title: Getting value from stored procedure in another stored procedure
body: <p>Sorry, lots of code coming up.. </p>
  
  <p>I saw another question like this that used output parameters. I'm using the RETURN statement to return the value I want to use. </p>
  
  <p>I have one stored procedure InsertMessage that looks like this:</p>
  
  <pre>
  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
  </pre>
  
  <p>which another stored procedure uses:</p>
  
  <pre>
  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
  </pre>
  
  <p>The row that causes the trouble and of which I'm unsure how to handle is this one:</p>
  
  <pre>
  SET @messageId = [dbo].[InsertNewMessage](@messageText, @dateTime, @bySMS, @byEmail)
  </pre>
  
  <p>The syntax seems ok because I can save it. When I run it I get the error message:</p>
  
  <pre>
  Running [dbo].[InsertMessageFromUserToGroup] ( @userEmail = test@test.com, @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].
  </pre>
  
  <p>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?</p>