Loop help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Loop help

Author

Topic

PHK
Starting Member

9 Posts

Posted - 2009-07-31 : 15:20:24

Here is my first try at a loop, it is not working and would appreciate any assistance. I want to make sure every userid is unique and if not add a digit to it.

DECLARE @i INT
DECLARE @id VARCHAR(64)
DECLARE @newid VARCHAR(64)
DECLARE @count INT

SET @id = 'this is the user id'
SET @newid = @id
SET @i = 0

SELECT @count = 0
WHILE 1 = 1
BEGIN
SELECT @count = (SELECT COUNT(*) FROM usertest WHERE userid = @newid)
IF @count = 0
EXEC('INSERT INTO usertest (userid) VALUES (' + @newid +')')
BREAK
ELSE
SET @i = @i + 1;
SET @newid = @id+@i;
CONTINUE
END

I am receiving a Syntax error near ELSE

Posted this in 2008 forum at first and then realized I was on the 2000 server instead of my 2008 server

Any help would be appreciated,

Thank you

PHK

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2009-07-31 : 15:39:08

Just a question, why add a digit to it.
Why not have the user choose a different one if it exists?

IF NOT EXISTS(SELECT FROM usertest WHERE userid = @newid)
   INSERT INTO usertest (userid) VALUES (@newid)
ELSE
RETURN 0

Get info back from the sp to alert the user.

Zath

PHK
Starting Member

9 Posts

Posted - 2009-07-31 : 15:45:04

I agree completely with you and like the idea, unfortunatily it was passed down to me that this is the way the organization wants to do it. I can try one last ditch effort to do this, but I also have 20,000 individuals that are about to be users that I am giving these userids to.
I created the userids yesterday by combining year signed up with last name and first name first initial and middle initial.
Ex. 09jonesdw. Now I am just making sure none of the 20,000 individuals I have in the database are the same and if they are to add that digit.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources