CODEWITHSUNDEEP
sql-server-2005
HashCoder
HashCoder

Reputation: 946

Random value generation in SQL server 2005

I have a User table, which is having Userid(primary key), Username, Password

There is a stored procedure which accepts list of users as XML. My requirement is to insert the users to User table, but before inserting I need to check whether the Username is already exists. If exists then a random number should be appended with username.

How do i check whether username already exists or not and randomise it, below is the sql which blindly inserts the username. Any help?

XML Structure:<ROOT><User UserId="0" Username="ajohn" Password="548788844" ></ROOT>

SQL:

INSERT INTO [User] (Username, Password) 
        SELECT tab.col.value('@Username','nVarchar(max)') AS Username,     tab.col.value('@Password','nVarchar(max)') AS Password,            
        FROM @pupilDetails.nodes('ROOT/User') tab(col)

Upvotes: 0

Views: 264

Answers (2)

Arion
Arion

Reputation: 31239

Maybe this will help:

--Existing data
DECLARE @User TABLE
(
    UserId INT PRIMARY KEY CLUSTERED IDENTITY(1,1),
    Username nVarchar(MAX),
    Password nVarchar(MAX)
)
INSERT INTO @User(Username,Password)
SELECT 'bob_456254','1584526'
UNION ALL
SELECT 'ajohn_4525846','4561584'
--Something has to separate the username and the number I used "username"_"randomnumber"
--The xml
DECLARE @pupilDetails XML='<ROOT><User UserId="0" Username="ajohn" Password="548788844" ></User></ROOT>'

;WITH CTE AS
(
    SELECT
        tab.col.value('@Username','nVarchar(max)') AS Username,
        tab.col.value('@Password','nVarchar(max)') AS Password

    FROM
        @pupilDetails.nodes('ROOT/User') AS tab(col)
)
INSERT INTO @User
(
    Username,
    Password        
)
SELECT 
    CTE.Username+
    (
        CASE WHEN EXISTS
                (
                    SELECT
                        NULL
                    FROM
                        @User AS Users
                    WHERE
                        SUBSTRING(Users.Username,0,CHARINDEX('_',Users.Username))=CTE.Username
                )
            THEN '_'+CAST(ABS(CHECKSUM(NewId())) AS VARCHAR(MAX))
            ELSE ''
        END
    ) AS UserName,
    CTE.Password  
FROM 
    CTE

SELECT * FROM @User

Upvotes: 1

Alex P
Alex P

Reputation: 12487

The following may help to get you started.

First I genrate a random number between 1 and 100. Second, I check to see if UserName already exists and either insert into DB or append the random number if already exists.

DECLARE @RandomInteger int
DECLARE @MaxValue int
DECLARE @MinValue int

SET @MaxValue = 100
SET @MinValue = 1

//Get random number between 1 and 100
SET @RandomInteger = ((@MaxValue + 1) - @MinValue) * RAND() + @MinValue

IF EXISTS (SELECT UserName FROM User WHERE UserName = @UserName)
  INSERT INTO [User] (UserName, Password)
  VALUES (@UserName + CAST(@RandomInteger AS VARCHAR), @Password)
ELSE
  INSERT INTO [User] (UserName, Password)
  VALUES (@UserName, @Password)

Disclosure: I'd considered myself a neophyte with SQL so might be worth waiting to see what other answers emerge to get a balanced view. I'd be interested myself.

Upvotes: 1

Related Questions