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 2008 Forums

Transact-SQL (2008)

Loop help

Author

Topic

PHK
Starting Member

9 Posts

Posted - 2009-07-31 : 14:32:52

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

Any help would be appreciated,

Thank you

PHK

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-07-31 : 14:52:08

What is not working? Are you getting an error or unexpected results?

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
	BEGIN
		INSERT INTO usertest (userid) VALUES (@newid)
		BREAK
	END

	SET @i = @i + 1;
	SET @newid = @id+@i; -- not sure how you want this added
END

PHK
Starting Member

9 Posts

Posted - 2009-07-31 : 14:59:18

Sorry yes it is returning Incorrect Syntax near ELSE.

TG
Master Smack Fu Yak Hacker

6065 Posts

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

for a totally different way you could to this. It assumes you have some type of UNIQUE constriant on UserID (which you should)


declare @id varchar(64)
       ,@i int       
       ,@errorstate int

set @id = 'this is the user id'
set @i = 0

while @i = 0 OR @errorstate is not null
begin
       begin try
              insert usertest (userid)
              select @id + isNull(convert(varchar(12), nullif(@i, 0)),'')
              set @errorstate = null
       end try
       begin catch
              set @errorstate = error_state()
              set @i = @i+1
       end catch
end

Be One with the Optimizer
TG

PHK
Starting Member

9 Posts

Posted - 2009-07-31 : 15:18:54

Thanks for the replys they work on my SQL Server 2008 but I was not even checking the other one and it is a 2000, looks like I will have to change a few things up on it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources