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]%.