Return an output parameter from SQL Server via a stored procedure and c#
18,955
Solution 1
you want ParameterDirection.Output
not ParameterDirection.ReturnValue
Also take it out of the return part, return should be used to return a status not a value
And if you do use return, I would do it after the transaction is committed not before
Solution 2
SqlCommand.Parameters.Add("@NewQuoteNumber", SqlDbType.Int).Direction = ParameterDirection .Output ;
<SqlCommand>.ExecuteNonQuery();
int NewQuoteNumber = int.Parse(SqlCommand.Parameters["@NewQuoteNumber"].Value .ToString ());
now you can use this value into your code .
Author by
Darren
Updated on July 11, 2022Comments
-
Darren almost 2 years
I am having a devil of a time getting an output value from SQL Server.
Here is my stored procedure:
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
And here is my 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.
I have tried taking @NewQuoteNumber out of the beginning and placing it after the AS with a DECLARE but that produces an error, too.