CODEWITHSUNDEEP
sql-servervb.net
Paks
Paks

Reputation: 1470

Getting return value from stored procedure in VB.NET

I have the following stored procedure:

ALTER PROCEDURE [dbo].[BK_NUMBER]
    (@Category NUMERIC(38, 0)) 
WITH EXECUTE AS CALLER
AS
   DECLARE @RunningNumber Int
   DECLARE @min Int
   DECLARE @max Int
   DECLARE @newRunningNumber Int

   BEGIN

      SELECT @RunningNumber = RunningNumber
      FROM PARKMARKENNUMMER
      WHERE PARKMARKENTYP_ID = @Category

      UPDATE PARKMARKENNUMMER 
      SET RUNNINGNUMBER = @RunningNumber + 1
      WHERE PARKMARKENTYP_ID = @Category 

      SELECT @newRunningNumber = RunningNumber
      FROM PARKMARKENNUMMER 
      WHERE PARKMARKENTYP_ID = @Category 

      RETURN @newRunningNumber;
End;

I try to get @newRunningNumber with this VB code:

 Dim sqlConnection As New SqlConnection(MSSQL_Helper.strConnectionBookit)
        Dim command As New SqlCommand("BK_NUMBER", sqlConnection)
        command.CommandType = CommandType.StoredProcedure
        command.Parameters.Add("@Category", SqlDbType.Int).Value = ID
        Dim returnParameter As SqlParameter = command.Parameters.Add("RetVal", SqlDbType.Int)
        returnParameter.Direction = ParameterDirection.ReturnValue
        sqlConnection.Open()
        command.ExecuteNonQuery()
        sqlConnection.Close()
        Dim returnValue As Integer = returnParameter.Value
        Return returnValue

But it returns always "0". What am I doing wrong?

Upvotes: 1

Views: 13681

Answers (2)

Steve
Steve

Reputation: 5545

While I agree with the other posts, OUTPUT parameter is the way to go, most people dont realize that you can put more than a single line into your command text. Because you can, you can convert something a DBA did into something you can use, without changing your stored procedure. For example:

CMD.CommandText = "DECLARE @return_value int;EXEC   @return_value = [dbo].[BK_NUMBER] (@Category = " & ID & ";SELECT @return_value"
Dim Ret as int32 = CMD.ExecuteScalar

This is just an example of what you can do. You should of coarse use parameters to avoid injection and have proper error handling, etc etc...

Upvotes: 2

zimdanen
zimdanen

Reputation: 5626

You're not actually returning the value:

RETURN @newRunningNumber

However, as Aaron Bertrand said, you should either set it as an OUTPUT parameter or SELECT it back out.

OUTPUT:

ALTER PROCEDURE [dbo].[BK_NUMBER]
(
    @Category NUMERIC(38, 0),
    @newRunningNumber INT
)

Dim newRunningNumber As SqlParameter("@newRunningNumber", SqlDbType.Int)
newRunningNumber.Direction = ParameterDirection.Output
// execute
Dim returnValue As Integer = newRunningNumber.Value

SELECT:

SELECT @newRunningNumber AS NewRunningNumber

Dim returnValue As Integer = CType(command.ExecuteScalar(), Integer)

Upvotes: 6

Related Questions