Hello everyone, I've got a problem and I hope someone can help me. I need to inset a registry using Store procedure in SQL server 2k but my primary key isn't a autonumeric, so I need to give it a next ID, but I don't know how to use trigger so I try to make samething like this...

CREATE PROCEDURE sp_Insert_InvXn
            @VInvNo nvarchar(50),
            @VIDClient int,
            @VIDContact int,
            @VIDItem int,
            @VIDProduct int,
            @VInvName nvarchar(50),
            @VInvDetalle nvarchar(50),
            @VInvQuant int,
            @VInvUPrice money,
            @VInvAmount money
AS
          DECLARE @VNextID int
          SELECT @VNextID = Max(ID) FROM InXn

    INSERT INTO InvXn (
            ID,
            InvNo,
            IDClient,
            IDContact,
            IDItem,
            IDProduct,
            InvName,
            InvDetalle,
            InvQuant,
            InvUPrice,
            InvAmount
            )
    VALUES (
            @VNextID + 1,
            @VInvNo,
            @VIDClient,
            @VIDContact,
            @VIDItem,
            @VIDProduct,
            @VInvName,
            @VInvDetalle,
            @VInvQuant,
            @VInvUPrice,
            @VInvAmount
            )
GO

But when I run it, it told me that de ID can not be NULL, that mean @VNextID isn't work.
SO, PLEASE TO TELL ME WHAT I'VE TO DO, THANKS EVERYONE !!!

CREATE PROCEDURE sp_Insert_InvXn
            @VInvNo nvarchar(50),
            @VIDClient int,
            @VIDContact int,
            @VIDItem int,
            @VIDProduct int,
            @VInvName nvarchar(50),
            @VInvDetalle nvarchar(50),
            @VInvQuant int,
            @VInvUPrice money,
            @VInvAmount money
AS
          DECLARE @VNextID int
          SELECT @VNextID = Max(ID)+1 FROM InXn

    INSERT INTO InvXn (
            ID,
            InvNo,
            IDClient,
            IDContact,
            IDItem,
            IDProduct,
            InvName,
            InvDetalle,
            InvQuant,
            InvUPrice,
            InvAmount
            )
    VALUES (
            @VNextID,
            @VInvNo,
            @VIDClient,
            @VIDContact,
            @VIDItem,
            @VIDProduct,
            @VInvName,
            @VInvDetalle,
            @VInvQuant,
            @VInvUPrice,
            @VInvAmount
            )
GO



“I sense many useless updates in you... Useless updates lead to defragmentation... Defragmentation leads to downtime...Downtime leads to suffering..Defragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" -- http://sqlservercode.blogspot.com/

SELECT @VNextID = Max(ID) FROM InXn

Modify the above line syntax like this to sort your problem.

SELECT @VNextID = ISNULL(Max(ID),0) FROM InXn

Cheers,
Pooja Falor

Hi,

Or alternatively you can use this one:

SELECT @VNextID = CASE
            WHEN [ID] IS NULL THEN 1
            ELSE [ID] + 1
          END
FROM InXn

Hope this Helps.

Njoy,
Lalit

Those are good beginnings. However, they are not ready for concurrent stored procedure calls. You must wrap them in a transaction or else a concurrent call to the same stored procedure could result in an attempt to enter the same ID number and (presuming you have a Primary Key Constraint defined) will cause a Primary Key Constraint Violation.

BEGIN TRANSACTION
SELECT -- rest of your select statement

INSERT -- rest of your insert statement
COMMIT TRANSACTION

David Lundell
Principal Consultant and Trainer
www.mutuallybeneficial.com

He there,

I have similar question, relevant to autoincrement a column value while inserting a record. But this time, it is regarding adding multiple records at a time than adding single row. Here in this post, I wrote the solution I know and was looking for better one.

 Previously, if we need to do single INSERT, I did get @maxid from target table and a new row is added after incrementing the @maxid ...

 'SET @COL02 = ... value you want to insert
 'SET @COL03 = ... value you want to insert

 SELECT @MAXID = ISNULL(MAX(PRIMARYCOL01),0) FROM TARGET_TABLE
 INSERT INTO TARGET_TABLE(PRIMARYCOL01, COL02, COL03)
    VALUES(@MAXID + 1, @COL02, @COL03)


If I apply similar idea to process, dynamically increment the PRIMARYCOL01 while adding multiple rows at a time, the query might look like this

 INSERT INTO TARGET_TABLE(PRIMARYCOL01, COL02, COL03)
    SELECT @MAXID + 1, COL02, COL03
      FROM SOURCE_TABLE

However, it did NOT solve the incremental issue as SELECTION of Rows is done before insertion starts. Therefore, every row will have same @maxID + 1 value, but not @maxID + 1, @maxID + 2, @maxID + 3 ... so on.

I had an Idea to deal this but unfortunately it does work only on Oracle. The following query gets the values for PRIMARYCOL01 as required.

    SELECT @MAXID + ROWID, COL02, COL03
      FROM SOURCE_TABLE

Later I fetched on web and found following link to reproduce ROWID in SQL Server: http://support.microsoft.com/default...;EN-US;q186133

I concept works great but damn, the recent query I wrote to get ROWID (of a Complex Query OUTPUT - 4039 rows) took 3 min 34 secs. Obviously, no one would every want to spend waiting 3 mins 34 secs to just get ROWIDs v/s running the Complex query to generate results without ROWID in just 8 secs.

I know, there are some DB professionals out there, so please let us know if there is any better solution for inserting multiple rows while incrementing a primary column value and / or generating the ROWID in SQL server

Shyam Arjarapu
http://www.arjarapu.com