SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Looping until condition met in sp
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

GregDDDD
Posting Yak Master

120 Posts

Posted - 02/25/2014 :  12:31:24  Show Profile  Reply with Quote
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

Edited by - GregDDDD on 02/25/2014 12:56:59

GregDDDD
Posting Yak Master

120 Posts

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

Greg
Go to Top of Page

jeffw8713
Aged Yak Warrior

USA
808 Posts

Posted - 02/27/2014 :  14:52:20  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000