How do I return a value from a stored procedure??

[Avenger001]

I use stored procedures to insert stuff into my sql database thru my web site.

When I insert something new, one of the fields in my database automaticly generated a new unique number for that entry. More specific : Data type = int, Identity = Yes, Identity Seed = 1, Identity Increment = 1.

Now I would like to get this number back...basicly someting like a return without having to do a select querry. Is this possible??

Here is how I call my stored procedure and execute it.

SqlCommand sqlCmd;
SqlParameter sqlPrm;

sqlCmd = new SqlCommand( "spInsertNew", sqlConnection );
sqlCmd.CommandType = CommandType.StoredProcedure;

sqlPrm = new SqlParameter( "@ReturnValue", 0 );
sqlPrm.Direction = ParameterDirection.ReturnValue;
sqlCmd.Parameters.Add( sqlPrm );

sqlCmd.Parameters.Add( "@_wDate", Convert.ToDateTime( cmdLine ) );
............
..........

sqlConnection.Open( );
sqlCmd.ExecuteNonQuery( );
sqlConnection.Close( );

Is there a way to get it thru the code here, or am I stuck in doing a select to get this value back?

[bigal1]

To the the value of either a return or an output parameter, simply access the parameter.value property. For your code it would be:


int x = sqlCmd.Parameters["@ReturnValue"].value;

Of course I'm assuming you're returning an integer here.

HTH
Al

[Avenger001]

The problem is that the value I need is not passed as a parameter, so I don't have access to it with the code you provided. But if I could return that value from my stored procedure, then it would work with the code provided.

But I have no clue how to return a value from a stored precedure. I will look into it, but if someone does now how, please let me know.

[nicolaan]

I think you're looking for the @@IDENTITY value
See SQL help.

Example (created from the working code I'm currently using)

CREATE PROCEDURE SomeTableInsert
@r_intIdentityField INT OUTPUT,
@v_Otherfields....

-- your insert statement

SET @r_intIdentityField= @@IDENTITY

RETURN @@ERROR


in VB:
Add the parameter @r_intIdentityField as an input/output type (direction property)
And do something like this:
If cmd.ExecuteNonQuery() > 0 Then ' Stored Proc was executed
If CInt(cmd.Parameters("@RETURN_VALUE").Value) = 0 Then
' No errors in Stored Proc
msgbox("IdentityField = " & cmd.Parameters("@r_intIdentityField").Value.toString())
else
msgbox("Error in SP, error# = " & cmd.Parameters("@RETURN_VALUE").Value.tostring())
endif
else
msgbox("Error executing SP")
endif


Something like this should work.

You might also want to update your results, something like this:

row.BeginEdit()
row("FieldName").Value = cmd.Parameters("@r_intIdentityField").Value
row.AcceptChanges()


When you want to do this last bit, make sure your column (in vb) is set to readonly = false.