对不起,很多代码即将发布..
我看到另一个使用输出参数的问题 . 我正在使用RETURN语句返回我想要使用的值 .
我有一个存储过程InsertMessage,如下所示:
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
另一个存储过程使用:
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
引起麻烦但我不确定如何处理的行就是这一行:
SET @messageId = [dbo].[InsertNewMessage](@messageText, @dateTime, @bySMS, @byEmail)
语法似乎没问题,因为我可以保存它 . 当我运行它时,我收到错误消息:
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].
好像无法找到其他存储过程 . 我已经尝试了不同的方法来调用该过程,但其他一切都失败了 . 有什么建议?
1 回答
尝试改变
至
请注意
SET
已更改为EXEC
,并且已从参数中删除括号 .有关详细信息,请参阅本文末尾的MSDN documenation中的示例 .