通过存储过程和c＃从SQL Server返回输出参数 [英] Return an output parameter from SQL Server via a stored procedure and c#

问题描述

我有段时间从SQL Server获取输出值。

I am having a devil of a time getting an output value from SQL Server.

这是我的存储过程：

ALTER PROCEDURE [dbo].[Insert_UnknownCustomer_Quote_Document]
-- Add the parameters for the stored procedure here
@NewDocumentFileName nvarchar(100),
@NewDocumentWordCount int,
@NewQuoteAmount money,
@NewQuoteNumber int OUTPUT = 0

AS

DECLARE @Today datetime
SELECT @Today = GETDATE()

BEGIN TRANSACTION
BEGIN TRY

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
SET NOCOUNT ON;


-- Insert statements for procedure here
INSERT INTO dbo.Customers(DateAdded)
VALUES (@Today)

INSERT INTO dbo.Quotes(CustomerID, QuoteAmount, QuoteDate)
VALUES (@@IDENTITY, @NewQuoteAmount, @Today)

SELECT @NewQuoteNumber = @@IDENTITY
INSERT INTO dbo.DocumentFiles(QuoteNumber, DocumentFileName, DocumentFileWordCount)
VALUES (@NewQuoteNumber, @NewDocumentFileName, @NewDocumentWordCount)

-- Return quote number
RETURN @NewQuoteNumber

END
COMMIT TRANSACTION
END TRY

BEGIN CATCH
ROLLBACK TRANSACTION
PRINT 'Transaction rolled back.'
END CATCH

这是我的C＃：

SqlParameter returnQuoteNumber = new SqlParameter("@NewQuoteNumber", SqlDbType.Int);
        returnQuoteNumber.Direction = ParameterDirection.ReturnValue;
        newSQLCommand.Parameters.Add(returnQuoteNumber);

这是我现在收到的错误：

Here is the error I am receiving now:

Procedure or function 'Insert_UnknownCustomer_Quote_Document' expects parameter '@NewQuoteNumber', which was not supplied.

我尝试从开头删除@NewQuoteNumber并将其放在带有DECLARE的AS后面，但这也会产生错误。

I have tried taking @NewQuoteNumber out of the beginning and placing it after the AS with a DECLARE but that produces an error, too.

推荐答案

您不希望 ParameterDirection.Output ParameterDirection.ReturnValue

也应将其从返回部分中取出，return应该用于返回状态而不是返回值

Also take it out of the return part, return should be used to return a status not a value

如果您使用return，我会在事务提交之后而不是

And if you do use return, I would do it after the transaction is committed not before

这篇关于通过存储过程和c＃从SQL Server返回输出参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！