loop help

Question

loop help

pkinnaman

SSC Enthusiast

Points: 109
More actions
July 31, 2009 at 1:24 pm

#203411

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

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

If you are using control of flow statements (IF, ELSE, WHILE) and you have more than one command in the block you need to use BEGIN and END.

If I were to do something that way I'd probably do something like this:

-- create table variable for testing

DECLARE @usertest TABLE(userid VARCHAR(50))

DECLARE @id VARCHAR(50)

DECLARE @newid VARCHAR(50)

DECLARE @count INT

-- test data

INSERT INTO @usertest

(

userid

)

SELECT

'user'

UNION ALL

SELECT

'user1'

UNION ALL

SELECT

'user2'

-- return existing test data

SELECT * FROM @usertest

SET @id = 'user'

SET @count = 1

SET @newid = @id

-- get next id

WHILE EXISTS (SELECT 1 FROM @usertest WHERE userid = @newid)

BEGIN

SET @newid = @id + CONVERT(VARCHAR(40), @count)

SET @count = @count + 1

END

-- got newest id insert it

INSERT INTO @usertest

(

userid

)

VALUES

(

@newid

)

SELECT * FROM @usertest

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

pkinnaman SSC Enthusiast Points: 109 More actions · Answer 2

Thanks Jack this is what I was looking for and I am now able to make sure each id is unique when making userids by combing year, last name, first name first initial, and middle initial. So now instead of

10jonesdw, 10jonesdw I am getting what is needed 10jonesdw, 10jonesdw1.

Thanks again really appreciate the help!

Pete

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

Hi Pete,

Ok... but there are better ways to do this. I guess I would first question the requirement of making alpha-numeric keys in the first place. An IDENTITY column would do especially since the natural key needs a modification of adding year and a sequence number to be unique.

And, I probably wouldn't use a While loop even though it'll probably be "good enough" for this case.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jan Van der Eecken SSCrazy Eights Points: 9029 More actions · Answer 4

Hi Pete,

Jack already solved your problem with the syntax error 'Near Else', and Jeff chipped in with a suggestion as well. But in order to help you out it would be great if you could tell us what exactly it is you want to accomplish. From what I understood from your code snippet you want to prevent someone registering an existing user name like 'IAmTheUser' by changing it to 'IAmTheUser1'? Give us some clarity please? And if I'm right, why would you want to do that?

Regards,

Jan

--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)
In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]

Paul White SSC Guru Points: 150467 More actions · Answer 5

Hey Jan,

It looks like a puzzle or test question to me, so there may not be a real requirement at all 🙂

Jack's solution seems to cover the key points, so my guess would be that PHK has his answer.

Which is nice 😉

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

pkinnaman SSC Enthusiast Points: 109 More actions · Answer 6

Sorry it took awhile to reply to everyone was out of town for the weekend. What I am doing is trying to create userids from a database which we are eventually going to use with gaggle.net and create userids and eventually passwords for students. I have had a few issues since I have been out of the SQL loop for a few years and things are slowly coming back to me.

What i did was create a userid taking graduation year(or the year they are suppose to) such as 2015 and pull out the last 2 digits, combine that with the last name, then the first initial of the first name as well as the middle initial. I also made sure that if the middle initial did not exist this would not cause issues as well as getting rid of hyphens or spaces in the last name.

So I created user names such as 15jonesdw, but I wanted to make sure each of these userids are unique since I am pulling from close to 20,000 students and they are all already in the database. I know there are probably much easier ways as many of you have mentioned, but I am trying to implement this as well as several other things all at once before school starts, which is only a few weeks away. Too bad this wasn't just for some school project I would already be completed and well on my way.

Thanks again for your help,

and any suggestions are great.

PHK

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 7

Thanks Pete... My recommendation still stands... Use an IDENTITY column and have the students remember their "number". I don't know what Gaggle.net is, but let the students create their own handles and associate it with that number.

Heh... you may actually be violating privacy by doing what your propose. Some may not want to publish their graduation year because of both hazing and the fact that they might have "stayed back". Also, some folks just don't want people to know that there's more than one person with the same name, etc, etc. There are no privacy issues with numbers instead of naturally derived keys.

I really believe that your plan is bad not just for the DB, but I also believe that you're opening up the school to the potential for lawsuits (no matter how silly they may seem).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

pkinnaman SSC Enthusiast Points: 109 More actions · Answer 8

Thank you Jeff, that is what I have been leaning to as well. Seems to just make the process that much easier.