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 = 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].
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?
Best Answer
Try changing
to
Notice that
SET
has been changed toEXEC
, 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.