Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2011
    Posts
    63

    Unanswered: My first Transaction....

    Hi guys, this is my first transaction that inserts 3 rows into 3 linked tables.

    ALTER PROCEDURE [dbo].[createAccount]

    @username char(20),
    @email char(50),
    @password char(20),
    @verified BIT,
    @enabled BIT,
    @verificationCode char(8),

    @class TINYINT,
    @gender char (6),
    @charName char (20),
    @strength tinyint,
    @agility tinyint,
    @magicalPower tinyint,
    @hp integer,
    @mana integer

    AS

    BEGIN TRY

    BEGIN TRANSACTION



    INSERT INTO Accounts ([username], [email], [password], [verified?], [enabled?], [verificationCode])
    VALUES (@username, @email, @password, @verified, @enabled, @verificationCode)

    INSERT INTO Characters([charId], [charName], [strength], [agility], [magicalPower], [hp], [mana], [doingSomething?], [class], [gender])
    VALUES (SCOPE_IDENTITY(), @charName, @strength, @agility, @magicalPower, @hp, @mana, 0, @class, @gender)

    INSERT INTO CharAdventure([charId])
    VALUES (SCOPE_IDENTITY())

    COMMIT

    END TRY
    BEGIN CATCH

    IF @@TRANCOUNT>0
    ROLLBACK

    END CATCH

    Is that the correct way to use scope_identity?
    The first table has a primary key that auto increments, and i want to add that value to both subsequent tables.

    Thanks

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Some sort of HR database?

    Be careful. If you add an identity to your second table, then suddenly that value will be inserted into your third table, rather than the value from the top table.

    It is better practice to assign the values of niladic functions such as SCOPE_IDENTITY or @@RowCount to variables immediately after the statements is executed, and then reference the variable from then on.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jun 2011
    Posts
    63
    Quote Originally Posted by blindman View Post
    Some sort of HR database?

    Be careful. If you add an identity to your second table, then suddenly that value will be inserted into your third table, rather than the value from the top table.

    It is better practice to assign the values of niladic functions such as SCOPE_IDENTITY or @@RowCount to variables immediately after the statements is executed, and then reference the variable from then on.
    The value in the frist table, second table, and third table is the same. Does this mean i dont need to change anything?

    How would i assign it to a value after the first table?

    Thanks!

  4. #4
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    No. The value may be the same now, but it need not be in the future. In case you get a rollback at some point in time, the identity values will not be the same in all three tables anymore.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by TheGateKeeper View Post
    ...
    How would i assign it to a value after the first table?
    You only need to apply some minor changes. As Blindman said, store the value of SCOPE_IDENTITY() in a variable, and use the variable from there on.
    Code:
    BEGIN TRANSACTION
    
    DECLARE @charId INT
    
    INSERT INTO Accounts ([username], [email], [password], [verified?], [enabled?], [verificationCode])
    VALUES (@username, @email, @password, @verified, @enabled, @verificationCode)
    
    SET @charId = SCOPE_IDENTITY()
    
    INSERT INTO Characters([charId], [charName], [strength], [agility], [magicalPower], [hp], [mana], [doingSomething?], [class], [gender])
    VALUES (@charId, @charName, @strength, @agility, @magicalPower, @hp, @mana, 0, @class, @gender)
    
    INSERT INTO CharAdventure([charId])
    VALUES (@charId)
    
    COMMIT
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  6. #6
    Join Date
    Jun 2011
    Posts
    63
    Quote Originally Posted by roac View Post
    No. The value may be the same now, but it need not be in the future. In case you get a rollback at some point in time, the identity values will not be the same in all three tables anymore.
    What do you mean they wont be the same? If the values change from their intended form it will cause my database to be incorrect.

    Quote Originally Posted by Wim View Post
    You only need to apply some minor changes. As Blindman said, store the value of SCOPE_IDENTITY() in a variable, and use the variable from there on.
    Code:
    BEGIN TRANSACTION
    
    DECLARE @charId INT
    
    INSERT INTO Accounts ([username], [email], [password], [verified?], [enabled?], [verificationCode])
    VALUES (@username, @email, @password, @verified, @enabled, @verificationCode)
    
    SET @charId = SCOPE_IDENTITY()
    
    INSERT INTO Characters([charId], [charName], [strength], [agility], [magicalPower], [hp], [mana], [doingSomething?], [class], [gender])
    VALUES (@charId, @charName, @strength, @agility, @magicalPower, @hp, @mana, 0, @class, @gender)
    
    INSERT INTO CharAdventure([charId])
    VALUES (@charId)
    
    COMMIT
    Thanks!

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    He simply means that when an identity value is used, but the transaction rolled back, that identity value is never used (by that table) again. Existing data is not affected.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •