Replace WHILE Loop with Recursive CTE(?)

Question

Post reply

Replace WHILE Loop with Recursive CTE(?)

kiril.lazarov.77

Hall of Fame

Points: 3742
More actions
July 16, 2014 at 2:50 pm

#397071

Hi Everyone,
I would like to replace the code below with a set based solution. Any help is appreciated.
DECLARE @Cat TABLE (Category VARCHAR(50),isProcessed BIT)
INSERT INTO @Cat VALUES ('Vegetable',0)
INSERT INTO @Cat VALUES ('Fruit',0)
INSERT INTO @Cat VALUES ('Animal',0)
DECLARE @f NVARCHAR(500) = ''
DECLARE @t VARCHAR(200) = ''
WHILE EXISTS (SELECT * FROM @Cat WHERE isProcessed = 0)
BEGIN
SELECT @t = (SELECT TOP 1 Category FROM @Cat WHERE isProcessed = 0)
SELECT @f = 'FORMSOF(Thesaurus,' + @t + ')'
SELECT @t AS Category, SomeTxt
FROM tbl
WHERE CONTAINS( SomeTxt, @f )
UPDATE @Cat
SET isProcessed = 1
WHERE Category = @t
END
You won't be able to test this it though. It returns 3 result sets, I just want to UNION them and I believe a recursive cte could do the trick. Thanks.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Can't even run it as is since we don't have the table tbl or any sample data for it.

Also, I really don't think a recursive CTE will be able to accomplish what you may be trying.

kiril.lazarov.77 Hall of Fame Points: 3742 More actions · Answer 2

Hi Lynn,

Thanks for your response. You would need to add the following synonyms to your thesaurus file (tsenu.xml) to see some sample data (located at ...\MSSQL\FTData\)

<expansion>

Vegetable

Vegetables

Veggies

</expansion>

<expansion>

Fruit

Fruits

</expansion>

<expansion>

Animal

Animals

Creatures

Creature

</expansion>

Then load it:

EXEC sp_fulltext_load_thesaurus_file 1033

Then create tbl, the full text catalog, the full text index and you should be able to see some sample data. Any questions, let me know and thanks again for trying to help.

CREATE TABLE [dbo].[tbl](

[Id] [int] IDENTITY(1,1) NOT NULL,

[Txt] [varchar](max) NULL,

CONSTRAINT [PK_Id] PRIMARY KEY CLUSTERED

([Id] ASC)

)

INSERT INTO tbl

SELECT 'Asdfghj, cvbn, veggies are good ... qwwerty' UNION ALL

SELECT 'poiu 12345, I love animals...to eat...dsfghf999 ... but hate vegetables xx dogs are creatures' UNION ALL

SELECT 'ZXCVBN, veggies are healthy, fruits are tasty'

CREATE FULLTEXT CATALOG ftc

AS DEFAULT

CREATE FULLTEXT INDEX ON tbl(Txt)

KEY INDEX PK_Id

ON ftc

--------------------------------------------------------

DECLARE @Cat TABLE (Category VARCHAR(50),isProcessed BIT)

INSERT INTO @Cat VALUES ('Vegetable',0)

INSERT INTO @Cat VALUES ('Fruit',0)

INSERT INTO @Cat VALUES ('Animal',0)

DECLARE @f NVARCHAR(500) = ''

DECLARE @t VARCHAR(200) = ''

WHILE EXISTS (SELECT * FROM @Cat WHERE isProcessed = 0)

BEGIN

SELECT @t = (SELECT TOP 1 Category FROM @Cat WHERE isProcessed = 0)

SELECT @f = 'FORMSOF(Thesaurus,' + @t + ')'

SELECT @t AS Category, Txt

FROM tbl

WHERE CONTAINS( Txt, @f )

UPDATE @Cat

SET isProcessed = 1

WHERE Category = @t

END

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 3

Not a solution but some advice... rCTEs are frequently slower than a While loop and almost always use gobs more logical reads. I would be so quick to replace a While loop with an rCTE.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

kiril.lazarov.77 Hall of Fame Points: 3742 More actions · Answer 4

Thanks for the advice. I got two working solutions but I'm sure it could be improved so I though I would post and see what experts say. What I currently do is, I insert each result set into a table variable from the while loop so that I have one large result set that includes all words (categories). I doubt this is the best way to do this though.