SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


loop help


loop help

Author
Message
pkinnaman
pkinnaman
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 22
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
Jack Corbett
  Jack Corbett
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18292 Visits: 14889
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
pkinnaman
pkinnaman
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 22
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
Jeff Moden
SSC Guru
SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)

Group: General Forum Members
Points: 84623 Visits: 41067
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jan Van der Eecken
Jan Van der Eecken
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2426 Visits: 6494
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)
Paul White
Paul White
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15508 Visits: 11354
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
SQLblog.com
@SQL_Kiwi
pkinnaman
pkinnaman
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 22
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
Jeff Moden
SSC Guru
SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)

Group: General Forum Members
Points: 84623 Visits: 41067
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
pkinnaman
pkinnaman
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 22
Thank you Jeff, that is what I have been leaning to as well. Seems to just make the process that much easier.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search