Need help in Loop - 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

Transact-SQL (2000)

Need help in Loop

Author

Topic

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2009-11-22 : 05:58:08

I've a View. After me run

select * from myNewID, it will return random string

NewIDValue
-----------------------------------------
44359E7B-BB4C-4648-B377-2045E00A68A9

Me also have a table that store IDValue. Let's say, my table as follow,

tIDValue
NewIDValue -- char(6)
----------------------

Actually, me want to code a loop that can get the random string uniquely from myNewID

My problem is,
1. i don't know how to make it my select * from myNewID return only 6 character

2. If select * from myNewID (return 6 character) and this random string EXIST in the tIDValue, then loop will continue to re-execute select * from tIDValue, then re-check this random string EXIST in the tIDValue or not. If not, loop will continue

3. If select * from myNewID (return 6 character) and this random string NOT EXIST in the tIDValue, then this random string will be Inserted into tIDValue -- loop will end. So, me will get a unique string

I know the sound is crazy but me just want to fully utilise the power of the stored procedure

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-22 : 07:13:22

I have made this test and it works for me:


create table idtest(newIdVAlue char(6))

declare @help char(6)
declare @rowcount int
set @rowcount=0
while @rowcount=0
begin
 select @help= right(newid(),6) 

 insert idtest select @help
 where not exists(select * from idtest where newIdValue = @help)
 
 select @rowcount=@@rowcount

end

select * from idtest

No, you're never too old to Yak'n'Roll if you're too young to die.

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2009-11-22 : 14:41:46

quote:
Originally posted by webfred

I have made this test and it works for me:

create table idtest(newIdVAlue char(6))

declare @help char(6)
declare @rowcount int
set @rowcount=0
while @rowcount=0
begin
select @help= right(newid(),6)

insert idtest select @help
where not exists(select * from idtest where newIdValue = @help)

select @rowcount=@@rowcount

end

select * from idtest

No, you're never too old to Yak'n'Roll if you're too young to die.

Hello mister,

tq very much

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources