Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
|
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 18th, 2006, 11:55 AM
Friend of Wrox
 
Join Date: Oct 2003
Posts: 290
Thanks: 24
Thanked 0 Times in 0 Posts
Default RETURN IDENTITY AFTER INSERT INTO SECOND TABLE

Hi,

I am trying to set the output parameter to return the identity of the new row inserted and have tried all this but to no avail:

--Select Max(ReportLayoutId) from tblReportLayoutString
   --SET @ReportLayoutIdFromDB = IDENT_CURRENT('tblReportLayout')
   SET @ReportLayoutIdFromDB = @@IDENTITY
   --SET @id = @@IDENTITY
   --SET @ReportLayoutIdFromDB = @id

Please note that I insert to the tblReportLayout first and then use the identity to populate the foreign key of the tblReportLayoutString. Could someone please help me?

This is the stored procedure

CREATE PROCEDURE dbo.asp_SaveReport
(
@UserID int,
@ModuleKey nvarchar (100),
@TemplateKey nvarchar(100),
@ReportLayoutId int,
@ReportOrder int=0,
@CultureId int,
@ReportLayoutName nvarchar(100),
@Description nvarchar(100),
@ReportLayoutXML ntext,

@ReportLayoutIdFromDB int output
)

AS

DECLARE @id int

set nocount on

IF @ReportLayoutId=-1
    BEGIN

        INSERT tblReportLayout (UserId, ModuleKey, TemplateKey, ReportOrder)
        VALUES (@UserID, @ModuleKey, @TemplateKey, @ReportOrder)

        INSERT tblReportLayoutString ( CultureId, UserId, ModuleKey, TemplateKey, ReportLayoutId, ReportLayoutName, [Description], ReportLayoutXML, CreatedBy, CreatedOn, ModifiedBy, ModifiedOn )
        VALUES (@CultureId, @UserID, @ModuleKey , @TemplateKey, IDENT_CURRENT('tblReportLayout'), @ReportLayoutName, @Description, @ReportLayoutXML, @UserID, GetDate(), @UserID, GetDate() )

   --Select Max(ReportLayoutId) from tblReportLayoutString
   --SET @ReportLayoutIdFromDB = IDENT_CURRENT('tblReportLayout')
   SET @ReportLayoutIdFromDB = @@IDENTITY
   --SET @id = @@IDENTITY
   --SET @ReportLayoutIdFromDB = @id

    END
ELSE

.....
....

GO
 
Old August 18th, 2006, 12:39 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

First off, this is the SQL LANGUAGE forum, and your question appears pertinent to SQL Server only, so it really belongs in that forum.

Anyway, using IDENT_CURRENT the way you are attempting isn't really a good idea, as the scope of this function is database-wide. Thus, in theory, if other users are also doing INSERTS, you might actually retrieve their IDENTITY value, and not the one you just assigned.

Use the scope_identity() function to retrieve the IDENTITY value you just inserted. This is preferable to @@IDENTITY because if the table you are inserting into has a trigger on it, and that does an INSERT, you may again get the wrong value with @@IDENTITY.

It's simplest and safer to save the IDENTITY value you just assigned immediately after the INSERT statement which created it. Since you want to return that value as an output parameter, I think you should just use that to store the value. Therefore, I don't think you need the @id local variable.

Try:
Code:
...

set nocount on

IF @ReportLayoutId=-1
    BEGIN                 

        INSERT tblReportLayout (UserId, ModuleKey, TemplateKey, ReportOrder)
        VALUES (@UserID, @ModuleKey, @TemplateKey, @ReportOrder);

        SET  @ReportLayoutIdFromDB = scope_identity();

        INSERT tblReportLayoutString (CultureId, UserId, ModuleKey,
            TemplateKey, ReportLayoutId, ReportLayoutName, [Description],
            ReportLayoutXML, CreatedBy, CreatedOn, ModifiedBy,   ModifiedOn)
        VALUES (@CultureId, @UserID, @ModuleKey,
            @TemplateKey, @ReportLayoutIdFromDB, @ReportLayoutName,  @Description,
            @ReportLayoutXML, @UserID, GetDate(), @UserID, GetDate() )

    END
ELSE
...
Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
 
Old August 19th, 2006, 05:51 PM
Friend of Wrox
 
Join Date: Oct 2003
Posts: 290
Thanks: 24
Thanked 0 Times in 0 Posts
Default

Hi Jeff,

I would like to express my sincere thanks to your reply. I will try this first thing on Monday morning and let you know how it goes. I had never used IDENT_CURRENT before but because @@IDENTITY was not work I had a go to see if it would work.

I also had never heard of scope_identity(). Thanks for this tip.

Cheers,

CP





Similar Threads
Thread Thread Starter Forum Replies Last Post
how can i alter table (primary key + identity ) keyvanjan SQL Server 2000 1 July 12th, 2007 06:49 AM
getting identity column from the table g_vamsi_krish SQL Server 2000 1 March 15th, 2006 05:05 PM
Alter a column as identity for table kasanar SQL Server 2000 5 July 6th, 2005 08:44 AM
Identity Insert Problems hortoristic SQL Server 2000 2 April 13th, 2004 12:03 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.