Return an output parameter from SQL Server via a stored procedure and c#

Question

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.

FYI: Scope_Identity() is a much wiser choice than @@IDENTITY. — HABO
– HABO, Commented May 18, 2012 at 3:54

SQLMenace · Accepted Answer · 2012-05-18 01:54:41Z

5

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

edited May 18, 2012 at 1:54

answered May 18, 2012 at 1:49

SQLMenace

136k25 gold badges212 silver badges227 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Darren Over a year ago

Thank you very much for the help. I will mark it as answer as soon as it lets me.

marc_s · Accepted Answer · 2012-05-18 05:30:54Z

1

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 .

edited May 18, 2012 at 5:30

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered May 18, 2012 at 3:26

kuldeep verma

3262 gold badges4 silver badges10 bronze badges

1 Comment

marc_s Over a year ago

Welcome to StackOverflow: if you post code, XML or data samples, please highlight those lines in the text editor and click on the "code samples" button ( { } ) on the editor toolbar to nicely format and syntax highlight it!

Daniel Alder · Accepted Answer · 2015-09-30 14:00:21Z

0

this line:

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

should be:

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

edited Sep 30, 2015 at 14:00

Daniel Alder

5,5232 gold badges51 silver badges60 bronze badges

answered Sep 30, 2015 at 13:33

Sorin Iancu

1

Collectives™ on Stack Overflow

Return an output parameter from SQL Server via a stored procedure and c#

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related