这是我的存储过程。测试时,总能得到正确的结果。
ALTER PROCEDURE [dbo].[AddSmoothieIngredients]
-- Add the parameters for the stored procedure here
@Query NVARCHAR(4000) ,
@SmoothieId INT ,
@CreatedDate DATETIME ,
@Status INT ,
@UserId INT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN TRY
BEGIN TRAN
IF @SmoothieId > 0
BEGIN
DELETE FROM dbo.SmoothieIngredients
WHERE SmoothieId = @SmoothieId;
EXECUTE (@Query);
END
ELSE
BEGIN
IF @UserId = 0
SET @UserId = NULL;
INSERT INTO dbo.Smoothie
( Name, CreatedDate, Status, UserId )
VALUES ( N'', @CreatedDate, @Status, @UserId );
SET @SmoothieId = SCOPE_IDENTITY();
SET @Query = REPLACE(@Query, 'sId', @SmoothieId);
EXECUTE (@Query);
END
COMMIT TRAN
RETURN @SmoothieId
END TRY
BEGIN CATCH
ROLLBACK
END CATCH
END
然而,当我使用dapper.net调用此存储过程时,总是返回-1。
using (var conn = OpenConnection())
{
var parameter = new { Query = query, SmoothieId = smoothieId, CreatedDate = createdDate, Status = status, UserId = userId };
return conn.Execute("AddSmoothieIngredients", parameter, commandType: CommandType.StoredProcedure);
}
很可能dapper.net无法获取存储过程的返回值。但我真的不知道如何修复它。请帮忙。