I know may be it will seem a newbie question, but i facing this problem.

I have created a table users where i have 3 columns

CREATE TABLE USERS
(ID INT IDENITITY(1,1),
USERNAME VARCHAR(10),
USERID INT)

Now every time there is new requirement to create a user i call a procedure to check if requested USERID is available in system or not, if not then i create that user

CREATE PROCEDURE CreateUser
@USERID INT,
@USERNAME VARCHAR(10),
AS

IF EXISTS (SELECT * FROM USERS WHERE USERID=@USERID)
    SELECT 'USER ALREADY EXISTS.'
ELSE
BEGIN
    INSERT INTO USERS (USERNAME,USERID) VALUES (@USERNAME,@USERID)
END

It is not fail proof to avoid duplicate value.
I check my database and found same  USERID was created twice.
I know may be some of you guys will think why don't i make this USERID unique, but unfortunately this table is shared by 2 different kind of users one with USERID and one with no USERID (where USERID will be null).

How i am going to intercept 2 request which have already pass first check (EXISTS) and doing INSERT for same USERID.
Any advise will be appreciated.
 

 Any expert advise?

Perhaps the duplicate data was already in the table, before you started using the CreateUser procedure with the EXISTS check?

By the way, one way to implement a unique constraint that ignores nulls is with a trigger:

CREATE TRIGGER IgnoreNullsOnUserId ON Users
FOR INSERT, UPDATE
AS

IF EXISTS
(SELECT i.UserID FROM inserted i
WHERE i.UserId IS NOT NULL
AND EXISTS (SELECT u.UserId FROM Users u WHERE u.UserId = i.UserId AND u.ID <> i.ID))
BEGIN
RAISERROR(16,1, 'Cannot insert duplicate UserId!')
ROLLBACK TRANSACTION
RETURN
END

Thanks Adriaan.

I was also thinking about trigger but was worried about locking :(.

Procedure is there since first day, and i checked both records were having same time stamp (but it only happen with record out of 400 records so far).
 

Locking shouldn't be an issue, as long as you have a primary key on that identity column, and a non-unique index on UserId.