StrataFrame Forum

Capturing the OUTPUT from an Insert Sproc

http://forum.strataframe.net/Topic4521.aspx

By Clayton Hoyt - 11/15/2006

Hi Folks

The insert stored procs created by the DDT return  the SCOPE_IDENTITY value. How do I capture this using the .SAVE() method?

Thanks!

Clay

By Trent L. Taylor - 11/15/2006

Is this a trick question? BigGrin  Once saved, the BO will contain this value in each of the rows that were updated in the Primary Key field.  This is done automatically (by default) when the PrimaryKeyIsAutoIncrementing property is set to True on a BO.
By Wil Cantrell - 12/30/2007

Lets say we have a composite primary key and one of the fields is auto increment, how do we get the value back from SQL? I can not set PrimaryKeyIsAutoIncrementing to true with a composite key.
 
By Alagarsamy Rajamannar - 1/1/2008

Hi,

 I had this same issue. But I solved it by using stored procedures, where you can return values through output parameters.

By Wil Cantrell - 1/9/2008

How? What do you write in the stored proc?

Here is a brief example of the stored proc I'm using. Where do I get the value of @AutoIncrementField in my BO if it's not in the mapped field after a Save()?

ALTER  PROCEDURE [x].[myProc]
    @AutoIncrementField INT OUTPUT,
    @foreignkeyfield UNIQUEIDENTIFIER,
    @value1 INT,
 @value2 INT
AS
INSERT INTO [X].[myTable]
(
    foreignkeyfield
    value1,
    value2
)
VALUES
(
    @foreignkeyField,
    @value1,
    @value2
)
SET @AutoIncrementField = @@IDENTITY
By Wil Cantrell - 1/16/2008

Any advice? I have to consider just taking the framework out of the equation alltogether and just write the insert code by hand and then reload back.
By StrataFrame Team - 1/17/2008

I have to consider just taking the framework out of the equation alltogether and just write the insert code by hand and then reload back.
 

I think that's what you're going to have to do... without some significant changes to the framework, there is no way for us to retrieve only one field of a compound primary key value when that one field is auto-incremented.