通过存储过程和C＃返回从SQL Server中的输出参数(Return an output para

我有一个时间去从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);

这是我现在收到的错误：

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

我曾尝试服用@NewQuoteNumber出开始的，用声明AS后加，但产生的错误，太。

Answer 1:

你想ParameterDirection.Output不ParameterDirection.ReturnValue

也把它拿出来回报部分，收益应该用于返回状态不是值

而如果你使用的回报，我会做交易之前不提交后

Answer 2:

SqlCommand.Parameters.Add("@NewQuoteNumber", SqlDbType.Int).Direction = ParameterDirection .Output ;

<SqlCommand>.ExecuteNonQuery();

int NewQuoteNumber = int.Parse(SqlCommand.Parameters["@NewQuoteNumber"].Value .ToString ());

现在你可以使用这个值到你的代码。

Answer 3:

这条线：

int NewQuoteNumber = int.Parse(SqlCommand.Parameters["@NewQuoteNumber"].Value .ToString ());<br />

应该：

int NewQuoteNumber = int.Parse(SqlCommand.Parameters["NewQuoteNumber"].Value .ToString ());

文章来源: Return an output parameter from SQL Server via a stored procedure and c#