https://community.blueprism.com/t5/Product-Forum/Return-values-from-a-stored-procedure-insert/m-p/90201#M40462 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 ) '------------------------------------' Wed, 28 Sep 2016 11:43:00 GMT anson_nelson_lo 2016-09-28T11:43:00Z https://community.blueprism.com/t5/Product-Forum/Return-values-from-a-stored-procedure-insert/m-p/90196#M40457 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 ? Mon, 26 Sep 2016 18:25:00 GMT https://community.blueprism.com/t5/Product-Forum/Return-values-from-a-stored-procedure-insert/m-p/90196#M40457 Graeme__Tacon 2016-09-26T18:25:00Z https://community.blueprism.com/t5/Product-Forum/Return-values-from-a-stored-procedure-insert/m-p/90197#M40458 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. Mon, 26 Sep 2016 21:38:00 GMT https://community.blueprism.com/t5/Product-Forum/Return-values-from-a-stored-procedure-insert/m-p/90197#M40458 John__Carter 2016-09-26T21:38:00Z https://community.blueprism.com/t5/Product-Forum/Return-values-from-a-stored-procedure-insert/m-p/90198#M40459 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. Tue, 27 Sep 2016 14:50:00 GMT https://community.blueprism.com/t5/Product-Forum/Return-values-from-a-stored-procedure-insert/m-p/90198#M40459 Graeme__Tacon 2016-09-27T14:50:00Z https://community.blueprism.com/t5/Product-Forum/Return-values-from-a-stored-procedure-insert/m-p/90199#M40460 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 Tue, 27 Sep 2016 15:01:00 GMT https://community.blueprism.com/t5/Product-Forum/Return-values-from-a-stored-procedure-insert/m-p/90199#M40460 John__Carter 2016-09-27T15:01:00Z https://community.blueprism.com/t5/Product-Forum/Return-values-from-a-stored-procedure-insert/m-p/90200#M40461 Thanks Tue, 27 Sep 2016 18:13:00 GMT https://community.blueprism.com/t5/Product-Forum/Return-values-from-a-stored-procedure-insert/m-p/90200#M40461 Graeme__Tacon 2016-09-27T18:13:00Z https://community.blueprism.com/t5/Product-Forum/Return-values-from-a-stored-procedure-insert/m-p/90201#M40462 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 ) '------------------------------------' Wed, 28 Sep 2016 11:43:00 GMT https://community.blueprism.com/t5/Product-Forum/Return-values-from-a-stored-procedure-insert/m-p/90201#M40462 anson_nelson_lo 2016-09-28T11:43:00Z