using stored pr in 2005

  • hi there;

    I have a stored procedure that was developed for sql server 2000 that gets the ID of the last entry and locks it to insert a new one by using this:

    SELECT

    @PersonID = (SELECT max(personID) FROM person_info WITH (HOLDLOCK)) + 1

    SET

    IDENTITY_INSERT person_info ON

    INSERT INTO person_info(

    personID

    , other columnames.....)

    VALUES (@personID, @whateverelse)

    Is there any reason why this would not be correct for 2005? is there a better way to do it?

    Thanks.

     

  • That query seems a little confusing:  If the person_info table already has an identity column, you shouldn't need to insert the id.

    something like this would do it:

    Declare @newinsert INT

    Insert into person_info (Othercolumnnames)

    Values (Othercolumnnames)

    Set @newinsert = Scope_identity()

    --@newinsert is now the id of the newly inserted record (note the lack of the personid in the query..the system does it for you.

     

    As a rule, avoid the SET IDENTITY_INSERT.....with a good design, overriding the Identity insert should be kept to a minimum....creates a lot of problems.

  • we have a multi-tenancy application with a base of over 200 clients all having at least 3 people with permissions to add records to that table at any given time, so the potential for data corruption is certainly an issue, considering the identity column of that table is used across the db.

    is the method you describe sufficient to ensure integrity in this case?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply