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

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 2

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