TSQL Insert Conundrum
I have a table. The table needs to store numeric values about the location, so initially I only had two columns without an incrementing column by specifying the following:
RefID | TypeID
1 | 1
1 | 3
1 | 6
2 | 3
3 | 5
3 | 6
Where the first column is a location reference and the second is actual values.
The problem was how can I decide what value should be assigned to the first column. The idea I have is to add an auto-incrementing field to generate these values so that I 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 - copying the value of the id column 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 against CaseID, and if this value is null then use value SCOPE_IDENTITY()
. However, t SCOPE_IDENTITY
is evaluated before insertion, and thus returns the last generated credential, not the new one.
I cannot use a unique value from a linked table as I need full tracking if the user is editing the values.
So, I know what I want to do, but I just don't have the knowledge or experience to do it.
So, to encapsulate: How can I generate a unique value for the first element of a set and return that value so that I can reuse it for the rest of that set?
Also you could answer if there is another, easier way. Please remember - I am new to SQL stuff, so I need to understand why something is happening, because I might need to change it in the future.
Update . After grabbing the coffee, I noticed an error in my code - the result of changing to three columns, so [ID] becomes RefID in the first part of the INSERT statement. This also undoes some of the code and thus partially changes the nature of my problem. Sorry for the misunderstanding this may cause.
a source to share
When inserting into a table with a column, identity
you won't specify anything for the column identity
:
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 an id insert or anything like that unless you want to explicitly control the number. In that case, you would not have installed it in identity
.
Also, I am guessing about your columns.
Edit: Ok, so it looks like you don't actually need an identity column. Instead, you need to generate the next number in the sequence if not provided. In this case, you can leave the ID column there - it doesn't hurt anything, but it might help later if you need 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 can be no duplicates.
a source to share