Frank, the DBA, pecked at his keyboard with feverish rapidity. His tongue pointed from his lips caused his tense breathing to be audible to the entire department. His eyes were glazed with that "don't bother me I am deep in code" look that staged off all but the bravest of co-workers. Amid the array of stored procedures that were spawned in Frank's day of zoning was one that was used to insert a row into a table and return the identity value that was just created so that other calls can utilize the value.
In this stored procedure, Frank utilized the @@IDENTITY system function to derive the new identity value:
CREATE PROCEDURE [dbo].[InsertMyTable]
(
@ColumnValue [varchar] (50)
)
AS
BEGIN
-- insert new record
INSERT INTO [Demo].[dbo].[MyTable]
(
[MyColumn]
, [MyDate]
)
VALUES
(
@ColumnValue
, GETDATE()
);
-- declare variable to return
DECLARE @id BIGINT;
-- set the value of the variable to @@IDENTITY
SET @id = @@IDENTITY;
-- return the new identity value
SELECT @id;
END
GO
In his unit testing he executed the following command and was satisfied that it was functioning as expected:
EXEC [dbo].[InsertMyTable] 'MyTestValue';
Although, later he was confounded by the fact that the stored procedure did not consistently return the identity value when utilized in conjunction with the application development environment. Frank scanned the Internet like a whale hunting for plankton. He read blogs, forums, technical articles and flipped through his mountain of books and magazines that somehow supported a rattling cage filled with hamsters at its peak. He could not identify any great explanation of why this inconsistency exists.
He experimented with the alternative system functions of SCOPE_IDENTITY and IDENT_CURRENT to no avail. Frustrated, he wandered out of the office in a daze, scratching his head and having a fierce conversation to himself - which subsequently frightened and cut short the smoke breaks of all that were hanging around the exit door of the office building.
It wasn't long before Frank found himself at the neighborhood pub sitting with his good friend and fellow data-geek, Kyle. While everyone else at the pub were ruing their latest blunder with their significant others, Frank and Kyle were commiserating about their latest coding challenges. With a significant air of resignation, Frank described his conundrum with @@IDENTITY - complete with dramatic use of waving hands and sound effects. Kyle, with cool confidence, took a sip of his tall glass of Sam Adams and said "Did you try the output clause of the INSERT statement?" - All drew quiet.
Frank's eyes lit up like a neon sign in the dark. It was as if the weight of the world was lifted from his shoulders. Frank swigged down the remainder of his beverage, patted Kyle on the shoulder and said "Order me another, I will be right back!". Frank raced back to the office so quickly he was not sure that he actually opened the door. He quickly revised his stored procedure:
CREATE PROCEDURE [dbo].[InsertMyTable]
(
@ColumnValue [varchar] (50)
)
AS
BEGIN
-- declaration of table variable
DECLARE @MyTableVar TABLE (IdentityColumn BIGINT);
-- performs the insert
INSERT INTO [Demo].[dbo].[MyTable]
(
[MyColumn]
, [MyDate]
)
OUTPUT INSERTED.IdentityColumn INTO @MyTableVar
VALUES
(
@ColumnValue
, GETDATE()
);
-- returns the new identity value as a dataset
SELECT IdentityColumn FROM @MyTableVar;
END
GO
Further testing proved that Frank's modification was effective and reliable. This moment of enlightenment was so inspiring that the cleaning lady of the office was later heard describing something that looked like a halo was emitting from Frank's head. With a hop and a kick of his heels, Frank sprinted back to the pub and, in his great appreciation, bought his friend Kyle a steak dinner. At that moment, Frank was deeply grateful for his network of friends and a deeper appreciation for the subtleties of Transact-SQL. Although, the behavior of @@IDENTITY still baffles him, he hasn't lost any sleep over it.