How to use the identity as a column value during an insert

Question

I have a stored proc containing an SQL statement which is something like:

CREATE PROCEDURE SaveUser
@UserName nvarchar(10),
@FirstName nvarchar(150),
@LastName nvarchar(150)

AS
BEGIN

    INSERT INTO Users (UserName, FirstName, LastName)
    VALUES (@UserName, @FirstName, @LastName)

    SELECT SCOPE_IDENTITY()

END

Some users cannot log into the system and do not have a username; however, the UserName field has a unique index on it so I would like to be able to insert the users ID #, which is an auto increment field, as the UserName.

Does anyone know of an MS SQL Server method or variable which would allow me to do something like?

INSERT INTO Users (UserName, FirstName, LastName)
VALUES (ISNULL(@UserName, SCOPE_IDENTITY()),@FirstName,@LastName)

Edit My intention right now is to use something like the following

DECLARE @NextID int
SET @NextID = IDENT_CURRENT(Users) + IDENT_INCR(Users)


INSERT INTO Users (UserName, FirstName, LastName)
VALUES (ISNULL(@UserName, @NextID), @FirstName, @LastName)

I would just like to know if there is a built in method which could guarantee that this would be consistent.

Answer 1

You could add an AFTER INSERT trigger, updating the UserName field with the value of the Identity column from the inserted table.

Answer 2

INSERT NULL or random string, then update using scope_identity afterwards

INSERT INTO Users (UserName, FirstName, LastName)
VALUES (ISNULL(@UserName, CAST(NEWID() AS varchar(50)),@FirstName,@LastName)

UPDATE Users
SET @UserName = CAST(SCOPE_IDENTITY() AS int)
WHERE UserId = SCOPE_IDENTITY()

(apologies if newid does not cast directly... can't test right now)

It could be done all in one with an aggregate but it's not reliable (2 calls) in quick succession etc)

Answer 3

It will have to be done in two steps. In this case I would simply use the NEWID() function to generate a random GUID value to use for the username and update it afterwards using an update statement. Since your using a Stored Procedure the amount of step you use in this case is not going to cause major issues.

Details on NEWID and it's use can be found here

Answer 4

Instead of duplicating the ID column, You could also solve this at retrieval time. For example, say you let the username be NULL for an anynomous user. You can then retrieve the username like:

select 
   UserName = IsNull(UserName,UserId)
from WebUsers

EDIT: If you like a UNIQUE constraint on names, you can use a calculated column:

create table WebUsers (
    id int identity,
    name varchar(12),
    uniqueid as isnull(name,id)
)

create unique index ix_webusers_uniqueid on WebUsers (uniqueid)

The column uniqueid is a calculated column, that translates to isnull(name,id) whenever you use it. With this setup, you can insert users in one query:

insert into WebUsers (name) values ('Dark Lord')
insert into WebUsers (name) values ('The Ring')
insert into WebUsers (name) values (NULL)
insert into WebUsers (name) values (NULL)

But not duplicate users; the following will bail out with an error:

insert into WebUsers (name) values ('The Ring')

You can query on uniqueid to find the username for named users, or the id for anynomous users:

select uniqueid from WebUsers

Answer 5

How about wrapping it in a transaction, and just using your spid for the UserName. Like this:

CREATE PROC BlahBlah
...
BEGIN
BEGIN TRAN UserInsert
BEGIN TRY

    INSERT INTO Users (UserName, FirstName, LastName)    
    VALUES (COALESCE(@UserName,@@SPID), @FirstName, @LastName)

    IF @UserName IS NULL
    BEGIN
    DECLARE @MyUserID int  
    SET @MyUserID = (SELECT SCOPE_IDENTITY())

    UPDATE Users
    SET UserName = @MyUserID
    WHERE UserID = @MyUserID
    END
END TRY
BEGIN CATCH
    ROLLBACK TRAN UserInsert
    RETURN
END CATCH
COMMIT TRAN UserInsert
END

A few other points: - This seems like a really weird requirement. You may want to have a look at your design. - Beware of SCOPE_IDENTITY() issues with parallel query plans. The OUTPUT clause is much safer at present, until the bug gets resolved.