I found this article very timely for me. I have a SQL function that does basic Proper casing of names and some matching criteria for the hundreds (eventually thousands I am guessing) of exceptions. In processing 18,197 rows with 6 calls to the function per select statement to change potential names the CURSOR ran 2x as fast as the "ARRAY". After reading the article, I am quite confused by this. Below is the code with the cursor

select statement

select name1,

dbname.dbo.proper(name1)

... to name6

... set up and simple exlcusion stuff

DECLARE properex CURSOR FAST_FORWARD FOR

SELECT Oldval, Cvtval FROM Proper_exceptions

where loc = 'M' order by sort

OPEN properex

FETCH NEXT FROM properex

INTO @old, @new

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.

WHILE @@FETCH_STATUS = 0

BEGIN

select @outputstring = replace(@outputstring,@old,@new)

-- This is executed as long as the previous fetch succeeds.

FETCH NEXT FROM properex

INTO @old, @new

END

CLOSE properex

DEALLOCATE properex

-- the table variable

... same set up and simple exclusion stuff

DECLARE @tbl TABLE(

RowID INT IDENTITY(1, 1),

ov VARCHAR(100),

nv VARCHAR(50))

/*Local variables */

DECLARE @old VARCHAR(100),

@new VARCHAR(50),

@ctr int, /*create local @@fetch_status*/

@cRow int /*row pointer (index)*/

/* create array simulator */

INSERT @tbl (ov,nv)

SELECT Oldval, Cvtval FROM Common.dbo.Proper_exceptions

where loc = 'M'

order by sort

set @ctr = @@ROWCOUNT

set @cRow = 1

WHILE @cRow <= @ctr

BEGIN

select @old = ov, @new = nv from @tbl

where RowId = @cRow

select @outputstring = replace (@outputstring,@old,@new)

set @cRow = @cRow + 1

END

-- the current size of the table is only 310 rows

after post comment - I should have read all the posts before adding mine. Sounds like cursors are more efficient in this case. I would appreciate other suggestions to my issue at hand. In try to proper case words - I already have a basic upper case first letter lower case the rest. I need an efficient way to loop through all my other criteria. For example for the name JOHN SMITH IV, CEO, CFO after the first pass would look like John Smith Iv Ceo, Cfo. I have a table with 'M' atching values of

_ = blanks

part of list of 300 match replacements

_ceo_ = _CEO_

_cfo_ = _CFO_

_Iv_ = _IV_

After passing thru all the replacements the final word is

John Smith IV CEO, CFO

Is there an easier way to process all of the potential replacements. Using the like statement I have filtered out all potential replacement words that do not have a letter match for the given word. For example the like clause for the example shown is %[johnsmitvcef]%.