Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

loop help Expand / Collapse
Author
Message
Posted Friday, July 31, 2009 1:24 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 4, 2009 7:06 AM
Points: 7, 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
Post #763350
Posted Friday, July 31, 2009 2:19 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:22 PM
Points: 11,148, Visits: 12,889
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

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
Post #763386
Posted Friday, July 31, 2009 3:02 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 4, 2009 7:06 AM
Points: 7, 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
Post #763399
Posted Saturday, August 1, 2009 8:39 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:06 PM
Points: 36,711, Visits: 31,160
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #763597
Posted Saturday, August 1, 2009 2:36 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, March 17, 2014 7:18 AM
Points: 2,439, Visits: 6,426
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)
Post #763668
Posted Monday, August 3, 2009 1:56 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:21 PM
Points: 11,192, Visits: 11,090
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #763926
Posted Monday, August 3, 2009 7:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 4, 2009 7:06 AM
Points: 7, 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
Post #764040
Posted Monday, August 3, 2009 8:19 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:06 PM
Points: 36,711, Visits: 31,160
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #764530
Posted Tuesday, August 4, 2009 7:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 4, 2009 7:06 AM
Points: 7, Visits: 22
Thank you Jeff, that is what I have been leaning to as well. Seems to just make the process that much easier.
Post #764773
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse