Looping until condition met in sp

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

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources