It should be possible to capture the return of a stored procedure. The trick, however, is to flip the invocation of the SP from an SQL command to an SQL query. As an example, here is my stored procedure that inserts a row and returns the ID:
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
INSERT INTO Technicians (FirstName, LastName) values (@LastName, @FirstName);
In Thingworx, I set up a service that invokes the SP as a Query instead of a Command. The result is an infotable, which can be later formatted and parsed via a datashape:
Testing the service inserts a row and returns the ID as expected: