Return values from a stored procedure insert

[Graeme__Tacon]

Hi, Does anyone know how we can return values from a stored procedure that is doing an insert ? The table we are insert to has an INDENTITY field, which increments with each insert. We want to pass that back to Blue Prism, so we can use that when accessing the table further down the line. The stored procedure is set up as follows: INSERT INTO ABCD.TABCD ( [Surname] ,[DateOfBirth] ) VALUES ( @insurName ,@indateOfBirth ) SET @outRequestID = SCOPE_IDENTITY() From Blue Prism, we are using the 'Data - SQL Server' object', but cannot get it to retrieve the @outRequestID return from the SP and pass it back to the calling process. Has anyone managed to do this ?

[John__Carter]

Hi Graeme - You'd have to look up the syntax, but from memory SQL has a variable called @@Identity that gets automatically set on each insert.

[Graeme__Tacon]

Thanks John. We know the Stored Procedure I've posted above is working and getting the value we want. What we can't work out, is how Blue Prism can pick that value up.

[John__Carter]

Hi Graeme - you might have to add a new page to the SQL business object to enable it to call a SP. Something along the lines of this: Using conn As New SqlConnection(connect) Using cmd As New SqlCommand(query, conn) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.AddWithValue(""@Category"", Category.Text) cmd.Parameters.Add(""@CategoryID"", SqlDbType.Int, 0, ""CategoryID"") cmd.Parameters(""@CategoryID"").Direction = ParameterDirection.Output conn.Open() cmd.ExecuteNonQuery() ID = cmd.Parameters(""@CategoryID"").Value End Using End Using

[Graeme__Tacon]

Thanks

[anson_nelson_lo]

Hi Graeme , You can try this but not sure it will work or not.Thanks Deaclare @outRequestID Bitint INSERT INTO ABCD.TABCD ( [Surname] ,[DateOfBirth] ) VALUES ( @insurName ,@indateOfBirth ) SET @outRequestID = SCOPE_IDENTITY() '------------------------------------' Select (@outRequestID) '------------------------------------' OR '------------------------------------' Return (@outRequestID ) '------------------------------------'