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 2012 Forums
 Transact-SQL (2012)
 Looping until condition met in sp

Author  Topic 

GregDDDD
Posting Yak Master

120 Posts

Posted - 2014-02-25 : 12:31:24
I've never written a loop in an sp and I'm concerned with run away code. The idea is to create a unique value based on a root value, parameters passed in, and then a random number. Compare that to a column in the database to see if it already exists, and if it doesn't return that value. If it does exist try again with a new random number, look up, etc.

Does this look like good logic below? @County and @Type are passed in as parameters.

Greg



DECLARE @ROOT_OID VARCHAR(22)
SET @ROOT_OID = '2.16.840.1.113883.17.7'

DECLARE @MPIOut VARCHAR(50)
DECLARE @MPILookUp VARCHAR(50)

DECLARE @Stop int
DECLARE @Lower int
DECLARE @Upper int
DECLARE @Random int
SET @Lower = 1 -- The lowest random number
SET @Upper = 99999 -- The highest random number

SET @Stop = 0

WHILE (@Stop=0) BEGIN

SELECT @Random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)

DECLARE @CountyCode VARCHAR(2)
SELECT @CountyCode = CASE WHEN @County = 1 THEN '.1' WHEN @County = 2 THEN '.2' ELSE '.0' END

DECLARE @TypeCode VARCHAR(2)
SELECT @TypeCode = CASE WHEN @Type = 1 THEN '.1' WHEN @Type = 2 THEN '.2' ELSE '.0' END

SELECT @MPIOut = @ROOT_OID + @CountyCode + @TypeCode + '.' + CAST(@Random AS VARCHAR)
--Look up the generated value to see it if has been used
SELECT @MPILookUp = [item_text] FROM [AICU].[dbo].[tbl_text] WHERE [item_id] = 11254 AND [item_text] = @MPIOut
--Set the control variable. If it = 1 then exit loop
SELECT @Stop = CASE WHEN @MPILookUp = '' THEN 1 WHEN @MPILookUp IS NULL THEN 1 ELSE 0 END

END

SELECT @MPIOut

GregDDDD
Posting Yak Master

120 Posts

Posted - 2014-02-25 : 12:32:56
Just a thought. @MPILookup could be NULL instead of an empty string ''

Greg
Go to Top of Page

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2014-02-27 : 14:52:20
If I am reading this correctly, then it really is not random - as it can be any value between 1 and 99999 at the end. This can be done without a loop - but I don't have the time to put together the code.

Basically, what I would do is build a table of all possible values using a Tally/Numbers table. Then you can outer join that table to your tbl_text table on the item_text column and filter for null values. This will give you a list of all available numbers and from that you can then randomly select one of the values and return it.

If you really have to use a loop, then I would move the @CountyCode and @TypeCode declarations outside the loop and change the logic to use an EXISTS statement where you reset @MPIOut to null if there is a match:

SET @MPIOut = {build up the MPIOut variable};

IF EXISTS (Select * From aicu.dbo.tbl_text Where item_id = 11254 And item_text = @MPIOut)
BEGIN
SET @MPIOut = NULL;
END

Change the WHILE loop to

WHILE (@MPIOut IS NULL)

Once you have a valid value, the loop will stop and return that value.
Go to Top of Page
   

- Advertisement -