Should I Be Using ExecuteNonQuery Like This

[Auguy]

VB.Net project. I have the following stored procedure in SQL. It works fine but I am executing it with

      ' Run Stored Procedure
      ...
myCommand.ExecuteNonQuery()
      myErrorVal = CInt(myCommand.Parameters("@myError").Value)
      NextInvoiceNbr = CInt(myCommand.Parameters("@NextInvoiceNbr").Value)

Should I beusing something besides ExecuteNonQuery()? I want to return the next invoice number, but I also want to check the error code to confirm the update worked.

ALTER PROCEDURE [dbo].[Usp_CompanyInvoiceNumber_Select_And_Update]
	@CompanyMasterFK int
	,@NextInvoiceNbr int output
	,@MyError int output
AS
BEGIN
	SET NOCOUNT ON;

	SELECT @NextInvoiceNbr = (SELECT NextInvoiceNbr From dbo.CompanyInvoiceNumber Where CompanyMasterFK = @CompanyMasterFK)

	SELECT @MyError = @@ERROR

	IF @MyError = 0
		BEGIN
			UPDATE dbo.CompanyInvoiceNumber
				Set NextInvoiceNbr = NextInvoiceNbr + 1
				,EditCount = EditCount + 1

				WHERE CompanyMasterFK = @CompanyMasterFK AND NextInvoiceNbr = @NextInvoiceNbr

			SELECT @MyError = @@ERROR
		END
END

Auguy
Sylvania/Toledo Ohio

 

[RyanEK]

Wrong forum, but to answer your question you can indeed use ExecuteNonQuery to return values from the store procedure but you need to set the direction of your return parameters to returnParameter.Direction = ParameterDirection.ReturnValue.

Alternatively you can use ExecuteReader which is used for getting the query results as a dataReader object.

 

[Olaf Doschke]

To quote MSDN: Executes a Transact-SQL statement against the connection and returns the number of rows affected.

So the direct return value of ExecuteNonQuery always is number of affected rows (just as side note: if you read the help topic about it, you learn this also includes rows only indirectly affected by triggers)

Typical usage therrefore is with non SELECT T-SQL, like Updates and Inserts. AS your stored proc defines @NextInvoiceNbr int output, you have an in/out parameter and the changes of @NextInvoiceNbr done within the code should refelct to myCommand.Parameters("@NextInvoiceNbr").Value, which works, correct?

You also get your error back this way, if you wnt a more natural channel for error exceptions, you would either do nothing and let the error propagate back to the client side or you would implement TRY/CATCH in T-SQL and use RAISEERROR to raise your own user defined error.

Bye, Olaf.

 

[Auguy]

Thanks to both of you for clarification. I do set the direction of the parameters, for some reason I didn't copy that code to the post.


Auguy
Sylvania/Toledo Ohio