Where to add BREAK in nested WHILE loops to exit when condition met 1

[waubain]

My question is where to add a BREAK or a GOTO on the inner loop so the innerloop does not have to perform all the comparisons once a comparison if found. Using a PRINT @DrugIter after the inner BEGIN it appears that it loops through all patterns before moving on to the next consult.

Backgound:
I could not come up with a set solution and this is my first attempt at a loop in T-SQL that I pieced together from examples found. Unfortunately the drug is in a free text field so I am trying to standardize the drug name(for counting purposes) by searching for a known drug name in our drug file against what is typed in the free text field. The below works, but in reality #drugfile is ~13000 names(fixed) and #consult is ~600 consults/month. This report is only need monthly to annually.

Here is my working mock up

IF OBJECT_ID('TEMPDB..#consult') IS NOT NULL DROP TABLE #consult
IF OBJECT_ID('TEMPDB..#drugfile') IS NOT NULL DROP TABLE #drugfile
IF OBJECT_ID('TEMPDB..#destination') IS NOT NULL DROP TABLE #destination
CREATE TABLE #destination(ConsultID int, RequestedDrug varchar(100)) 
CREATE TABLE #consult(ConsultID int, TextResult varchar(100), ConRowID int)
INSERT INTO #consult VALUES 
                       (10, 'lantus pen', 1)
                      ,(22, 'Lanoxin', 2)
                      ,(43, 'zolpidem 10mg q hs prn', 3)
                      ,(49, 'lansoprazole powder 1 pkt via gt ac', 4)
                      ,(15, 'levitra', 5)
                      ,(62, 'benefiber', 6)
                      ,(79, 'imiquimod 5% cream', 7)
                      ,(18, 'omeprazole 20mg 30 minutes before meals', 8)
                      ,(39, 'digoxin 0.0125mg QAM', 9)
                      ,(14, 'esomeprazole', 10)

CREATE TABLE #drugfile(DrugID int, SearchDrug varchar(100), RequestedDrug varchar(100), DrugRowID int)
INSERT INTO #drugfile VALUES 
                       (1001, 'LEVITRA', 'LEVITRA', 1)
                      ,(2023, 'OMEPRAZOLE', 'OMEPRAZOLE', 2)
                      ,(4336, 'ZOLPIDEM', 'ZOLPIDEM', 3)
					  ,(5643, 'LANOXIN', 'DIGOXIN', 4)
					  ,(5643, 'DIGOXIN', 'DIGOXIN', 5)

DECLARE @ConMaxRowsCount int
SET @ConMaxRowsCount = (SELECT MAX(ConRowID) FROM #consult)
DECLARE @ConIter int
SET @ConIter = (SELECT MIN(ConRowID) FROM #consult)

DECLARE @DrugMaxRowsCount int
SET @DrugMaxRowsCount = (SELECT MAX(DrugRowID) FROM #drugfile)
DECLARE @DrugIter int
SET @DrugIter = (SELECT MIN(DrugRowID) FROM #drugfile)

DECLARE @pattern varchar(100)


WHILE @ConIter <= @ConMaxRowsCount
BEGIN
	WHILE @DrugIter <= @DrugMaxRowsCount
		BEGIN
			PRINT @DrugIter
			SET @pattern = (SELECT TOP(1) SearchDrug FROM #drugfile WHERE @DrugIter = DrugRowID)
			IF (SELECT COUNT(*) FROM #consult WHERE ConRowID = @ConIter AND ([TextResult] = @pattern OR [TextResult] LIKE @pattern+'[^a-z]%' OR [TextResult] LIKE '%[^a-z]'+@pattern)) > 0
				INSERT INTO #destination (ConsultID, RequestedDrug) SELECT ConsultID, (SELECT TOP(1) [RequestedDrug] FROM #drugfile WHERE @DrugIter = DrugRowID)  FROM #consult
				WHERE ConRowID = @ConIter
				
			SET @DrugIter = @DrugIter + 1
		END
	SET @ConIter = @ConIter + 1
	SET @DrugIter = 0
END

SELECT  * FROM #destination

You don't know what you don't know...

 

[gmmastros]

try this:

WHILE @ConIter <= @ConMaxRowsCount
BEGIN
	WHILE @DrugIter <= @DrugMaxRowsCount
		BEGIN
			PRINT @DrugIter
			SET @pattern = (SELECT TOP(1) SearchDrug FROM #drugfile WHERE @DrugIter = DrugRowID)
			IF (SELECT COUNT(*) FROM #consult WHERE ConRowID = @ConIter AND ([TextResult] = @pattern OR [TextResult] LIKE @pattern+'[^a-z]%' OR [TextResult] LIKE '%[^a-z]'+@pattern)) > 0
			  [!]Begin[/!]
				INSERT INTO #destination (ConsultID, RequestedDrug) SELECT ConsultID, (SELECT TOP(1) [RequestedDrug] FROM #drugfile WHERE @DrugIter = DrugRowID)  FROM #consult
				WHERE ConRowID = @ConIter

				[!]SET @DrugIter = @DrugMaxRowsCount
			  End[/!]
			SET @DrugIter = @DrugIter + 1
		END
	SET @ConIter = @ConIter + 1
	SET @DrugIter = 0
END

If you find a match, set the loop counter suitably large so that it will exit the while loop.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[djj55]

Have you thought about a conversion/lookup table? Where you have the standard name for many non-standard?

Aspirin - Aspirin
Aspirin - Bayer
Aspirin - acetylsalicylic acid


djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[waubain]

George: Thank you for the suggestion. It worked as expected, and also helped me understand better the BEGIN/END statements.

djj: What you cannot see in this mockup is that #drugfile is a lookup table. The SearchDrug field is your second column and RequestedDrug is the your first column. There is not any type of PK/FK fields to join on the consult and drug file if that was where you were going.

Regards,
Bob

You don't know what you don't know...