Return value from stored procedure

Question

Hi I am using vs 2008 doing a winforms app in vb.net. I have a database with a single table, that has two columns, in it and a stored procedure. The stored procedure is supposed to take an input value, match that value against one column in the table and then return the corresponding value from the other column; except it doesnt.It returns 1 or 0 please can anyone tell me why

ALTER PROCEDURE dbo.getgamenumber(@outputnumber bigint OUTPUT,
                                  @inputnumber bigint) 
AS 

  SELECT @outputnumber = ggnumber
    FROM statstable
   WHERE gindex = @inputnumber 

  RETURN  @outputnumber

and the stored proc is called thus

With cmdgetgame
  .CommandType = CommandType.StoredProcedure
  .CommandText = "getgamenumber"
  .Parameters.Add("@outputnumber", SqlDbType.BigInt).Value = outputnumber
  .Parameters("@outputnumber").Direction = ParameterDirection.Output
  .Parameters.AddWithValue("@inputnumber", inputvalue)
  returnvalue = cmd.ExecuteScalar()
End With

Thanks for all and any help.

sorry for the confusion my vb code now looks like this

 With cmdgetgame
                .CommandType = CommandType.StoredProcedure
                .CommandText = "getgamenumber"
                .Parameters.Add("@outputnumber", SqlDbType.BigInt).Value = outputnumber
                .Parameters("@outputnumber").Direction = ParameterDirection.Output
                .Parameters.AddWithValue("@inputnumber", inputvalue)
                cmd.ExecuteNonQuery()
                returnvalue = cmdgetgame.Parameters("@outputnumber").Value
            End With

and my stored proc like this

ALTER PROCEDURE dbo.getgamenumber

( @outputnumber bigint , @inputnumber bigint) AS SELECT ggnumber FROM statstable WHERE (gindex = @inputnumber)

but I still dont get the value I expect.

Answer 1

You could execute the stored procedure with ExecuteNonQuery, and then check the value of the output parameter after. Like:

cmd.ExecuteNonQuery()
returnvalue = .Parameters("@outputnumber").Value

Answer 2

You seem to be trying to use @outputnumber as both an output parameter and a return value.

You don't need to set a value to that parameter in VB.NET when you set it to an output parameter.

Then, you don't need the return statement. (and you shouldn't use RETURN in T-SQL to get values out, anyway)

To get the output value:

cmd.ExecuteNonQuery()
returnvalue = cmdgetgame.Parameters("@outputnumber").Value

Answer 3

cmd.ExecuteScalar() will return the 1st column of the 1st row of the result set. Amend your procedure to:

SELECT @outputnumber

instead of

 RETURN @outputnumber

Answer 4

Blimey I've done it. Thanks to all who offered help, it showed me how skewed my initial attempt had been. My vb code now looks like this

 With cmdgetgame
                .Connection = _con
                .CommandType = CommandType.StoredProcedure
                .CommandText = "getgamenumber"
                .Parameters.AddWithValue("@inputnumber", inputvalue)
                .Parameters.Add("@retvalue", SqlDbType.Int).Direction = ParameterDirection.ReturnValue
                .ExecuteNonQuery()
                returnvalue = cmdgetgame.Parameters("@retvalue").Value


            End With

and my stored proc now looks like this

ALTER PROCEDURE dbo.getgamenumber

(

@inputnumber int

)

as declare @retvalue int

SELECT @retvalue = ggnumber FROM statstable WHERE (gindex = @inputnumber)

return @retvalue

Pheweee I thought I would never get there. Thanks again.