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.

Solution 1

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.

Solution 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

Solution 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

Solution 4

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

View more solutions

Comments

[Andomar]

That would leave a stored procedure with an output parameter that expects you to read its result set. It would work though :-)

[simon]

oppps 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