TSQL Insert Conundrum

Question

I have a table. The table needs to store a number values about a location, so initially I had just the two columns without the incrementing column, giving the following:

RefID | TypeID
    1 | 1
    1 | 3
    1 | 6
    2 | 3
    3 | 5
    3 | 6

Where the first column is the reference for a location and the second is the actual values.

The problem was how do I decide which value to give the first column. The idea I have is to add an auto-incrementing field to generate these values so I would have the following data instead:

ID | RefID | TypeID
1  | 1   | 1
2  | 1   | 3
3  | 1   | 6
4  | 4   | 3
5  | 5   | 5
6  | 5   | 6

So the autonumber column (column 1) acts as a seed for the reference column.

So I have two problems - the copying of the identity column value to the reference column, and returning the reference value to the application so it can be used if there is more than one value for the location.

I came up with this stored procedure:

CREATE PROCEDURE [dbo].[AddCaseType]
(
    @TypeID INTEGER,
    @CaseID INTEGER = NULL OUT 
)
AS
BEGIN

    INSERT INTO
     dbo.CaseTypeList(RefID, TypeID)
    VALUES
      ( ISNULL(@CaseID,SCOPE_IDENTITY()), @TypeID)

    Set @CaseID =  SCOPE_IDENTITY()
END
GO

ISNULL checks for NULL on CaseID and if it is null to use the SCOPE_IDENTITY() value. Howevert SCOPE_IDENTITY is evaluated prior to the insert and thus returns the last identity generated and not the new one.

I can't use a unique value from a related table as I need full tracking if a user edits the values.

So I know what I want to do, but just don't have the knowledge or experience to do it.

So to encapsulate: How can I generate a unique value for the first item of a set and return that value so I can reuse it for the rest of that set?

Also you could answer if there is another, simpler way. Please remember - I am a newbie at SQL stuff, so I need to understand why something is happening because I might need to change it in the future.

Update: After grabbing a coffee I noticed an error in my code - a result of the change to three columns, so [ID] becomes RefID in the first part of the INSERT statement. This also invalidates some of the code and thus partly changes the nature of my problem. Sorry for the confusion this may cause.

Answer 1

When inserting into a table with an identity column, you don't specify anything for the identity column:

DECLARE @ERR INT

INSERT INTO CaseTypeList (TypeID, CaseID) --Column1 is auto-number, skip it
VALUES (@TypeID, @CaseID)

-- capture error var and last inserted identity value
SELECT @ERR = @@ERROR, @ID = SCOPE_IDENTITY()

-- IF @ERR <> 0 handle error, otherwise return

You don't need identity insert or anything like that unless you want to explicitly control the number. In that case, you wouldn't set it to identity.

Also, I kind of guessed on your columns.

---

Edit: OK, so it looks like you don't actually need the identity column at all. Instead, you need to generate the next number in a sequence if one isn't provided. In this case, you can leave the identity column in there--it won't hurt anything, but might help later if you want a single unique key:

CREATE PROCEDURE [dbo].[AddCaseType]
(
    @RefID     INTEGER = NULL OUT,
    @TypeID    INTEGER
)
AS

BEGIN TRANSACTION

  IF @RefID IS NULL BEGIN
    SELECT @RefID = MAX(RefID)+1 FROM CaseTypeList
  END

  IF @@ERROR <> 0 BEGIN ROLLBACK; RAISERROR('Could not get ID', 16, 1) END


  INSERT INTO
    CaseTypeList(RefID, TypeID)
  VALUES
    (@RefID, @TypeID)

  IF @@ERROR <> 0 BEGIN ROLLBACK; RAISERROR('Could not insert', 16, 1) END

COMMIT TRANSACTION

Note: you should probably have a Unique Key Constraint on RefID and TypeID if there cannot be duplicates.