Geek asks and answers

Return value from stored procedure

Hi, I am using vs 2008 using the winforms application in vb.net. I have a database with one table in which there are two columns, as well as a stored procedure. The stored procedure must take an input value, match this value with one column in the table, and then return the corresponding value from another column; other than that doesnt.It returns 1 or 0, can someone please 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 saved proc is called that way

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 everything and for any help.

Sorry for the confusion, my vb code now looks like

 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 procedure like this

ALTER PROCEDURE dbo.getgamenumber
( @outputnumber bigint ,
@inputnumber bigint) 
AS 
    SELECT       ggnumber
    FROM            statstable
    WHERE        (gindex = @inputnumber)

but I still do not get the expected value.

+2
source share
4 answers

, . , , , . vb :

 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


ALTER PROCEDURE dbo.getgamenumber
(
@inputnumber int
) 
as
    declare @retvalue int 

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

    return @retvalue

Pheweee , . .

+3

, @outputnumber , .

VB.NET, .

return. ( RETURN T-SQL, , )

:

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

ExecuteNonQuery, . :

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

cmd.ExecuteScalar () will return the 1st column of the first row of the result set. Change the procedure:

SELECT @outputnumber

instead

 RETURN @outputnumber
+1
source

More articles:


All Articles