Get the return value from a stored procedure

I just burt more hours than I care to admit figuring out this one. I was using an ObjectDataSource to call a stored proc which inserts records into a couple of tables, then returns the identity of the second table. No matter what I did, I could not retrieve the return_value. Instead, I got "Nothing" in the RETURN_VALUE output parameter.

After reading numerous posts out there, I finally figured out that a Strongly Typed Dataset will populate the RETURN_VALUE with the first column of the first row of the resulting result set. Since my stored proc wan't returning a result set, I was getting nothing. By simply ending the proc with the following, I got my RETURN_VALUE:

   -- Do inserts and stuff...

   SELECT Scope_Identity()
   RETURN Scope_Identity()
END

By leaving the RETURN in there, I'm covered if the default behavior changes in the future.