CODEWITHSUNDEEP
performancet-sqlsql-server-2008stringcursor
RPM1984
RPM1984

Reputation: 73112

Can a CURSOR Be Avoided here?

I have a scalar UDF which is responsible for returning a NVARCHAR(MAX) with unique words in it. Don't ask why. :)

Sample Input: "pizza pinapple salami pizza cheese cheese"

Sample Output: "pizza pinapple salami cheese".

Here's the UDF:

ALTER FUNCTION [dbo].[ToUniqueString]
(
    @NonUniqueString NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @ReturnValue NVARCHAR(MAX)

    -- Split the string by spaces.
    DECLARE @Words TABLE (Word NVARCHAR(MAX))
    INSERT INTO @Words
    SELECT Word FROM dbo.SplitText(@NonUniqueString , ' ')

    -- Cursor through the records, creating a unique string.
    DECLARE @CurrentWord NVARCHAR(MAX)
    DECLARE @UniqueString NVARCHAR(MAX) = ''

    DECLARE WordCursor CURSOR FOR SELECT DISTINCT Word FROM @Words
    OPEN WordCursor
    FETCH NEXT FROM WordCursor INTO @CurrentWord
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @UniqueString = @UniqueString + ' ' + @CurrentWord

        FETCH NEXT FROM WordCursor INTO @CurrentWord
    END
    CLOSE WordCursor
    DEALLOCATE WordCursor

    RETURN RTRIM(LTRIM(@UniqueString ))
END

Can it be done without a cursor? Is a WHILE loop more efficient? What about FOR XML?

Just looking for the most efficient way to achieve the requirement.

Upvotes: 3

Views: 130

Answers (1)

Mitch Wheat
Mitch Wheat

Reputation: 300529

It can be done replacing the cursor with FOR XML:

ALTER FUNCTION [dbo].[ToUniqueString] 
( 
    @NonUniqueString NVARCHAR(MAX) 
) 
RETURNS NVARCHAR(MAX) 
AS 
BEGIN 
    DECLARE @ReturnValue NVARCHAR(MAX) 

    -- Split the string by spaces. 
    DECLARE @Words TABLE (Word NVARCHAR(MAX)) 
    INSERT INTO @Words 
    SELECT Word FROM dbo.SplitText(@NonUniqueString , ' ') 

    DECLARE @UniqueString NVARCHAR(MAX) = '' 

    set @UniqueString = 
    stuff( 
        (SELECT Distinct  ' ' + Word
         FROM @Words 
         for xml path('')) 
    , 1, 1, '')   

    RETURN @UniqueString
END

Upvotes: 2

Related Questions