CODEWITHSUNDEEP
sql-servervb.net
simon
simon

Reputation: 127

Return value from stored procedure

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.

Upvotes: 2

Views: 17845

Answers (4)

simon
simon

Reputation: 127

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.

Upvotes: 3

Mark PM
Mark PM

Reputation: 2919

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

Upvotes: 1

Andomar
Andomar

Reputation: 238048

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

Upvotes: 1

Andrew Barber
Andrew Barber

Reputation: 40139

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

Upvotes: 3

Related Questions