November 16, 2011 at 9:56 am
I have a name field that I'm trying to retrieve the suffix (JR, SR) from. I have a table of valid suffixes that I want to loop through (yes, a cursor). I'm storing the manipulated data in a temp table. Once I get all the names into the temp table, I want to update a column (#SuffixedNames.SuffixStartPosition) with the starting position of the suffix I found.
The problem is in the second cursor, which is highlighted below.
FETCH FIRST FROM SuffixesCursor INTO @WhichSuffix
WHILE @@Fetch_Status = 0
BEGIN
EXEC ('Update #SuffixedName SET SuffixStartPosition = CHARINDEX(@WhichSuffix, LastNameAdjusted)')FETCH NEXT FROMSuffixesCursor INTO @WhichSuffix
END
The message I get is Must declare the scalar variable "@WhichSuffix". I understand it's a scope issue, but I'm at a loss how to make the fetched value available to the EXEC statement.
If there's a better way to do this, that's fine too.
Thanks,
Mattie
Here's the complete code:
DECLARE @WhichSuffixVARCHAR(10)
DECLARE @ExecDeclareVARCHAR(100) =
' DECLARE @CursorSuffix VARCHAR(10) = @WhichSuffix'
DECLARE @ExecInsertVARCHAR(100) =
' INSERT INTO #SuffixedNames ( SSN, LastName, LastNameAdjusted)'
DECLARE @ExecSelectVARCHAR(100) =
' SELECT SSN, LastName, LastName'
DECLARE @ExecFromVARCHAR(100) =
' FROM PersonnelMaster pm'
DECLARE @ExecWhereVARCHAR(100) =
' WHERE SSN NOT IN (SELECT SSN FROM #SuffixedNames) AND LastName LIKE ''%'
DECLARE @ExecStatementFixedVARCHAR(500) =
@ExecInsert + @ExecSelect + @ExecFrom + @ExecWhere
DECLARE @ExecStatementVARCHAR(500)
CREATE TABLE #SuffixedNames (
UIDINT IDENTITY(1,1),
SSNCHAR(9),
LastNameVARCHAR(35),
LastNameAdjustedVARCHAR(35),
SuffixVARCHAR(10),
SuffixStartPositionTINYINT)
DECLARE SuffixesCursor CURSOR STATIC
FORSELECTCode
FROMNameSuffixes ns
ORDER BY
LEN(Suffix) DESC
INSERT INTO #SuffixedNames(
SSN,
LastName ,
LastNameAdjusted )
SELECTSSN,
LastName,
REPLACE(REPLACE(LastName, ',', ' '), '.', '') AS LastNameNoComma
FROMPersonnelMaster pm
WHERELastName <> REPLACE(LastName, ',', ' ')
ANDLastName NOT LIKE '%Jus%'
OPEN SuffixesCursor
FETCH FIRST FROM SuffixesCursor INTO @WhichSuffix
WHILE @@Fetch_Status = 0
BEGIN
SET @ExecStatement = @ExecStatementFixed + ' ' + @WhichSuffix + '%' + ''''
EXEC ( @ExecStatement)
FETCH NEXT FROMSuffixesCursor INTO@WhichSuffix
END
CLOSE SuffixesCursor
UPDATE #SuffixedNames
SETLastNameAdjusted = REPLACE(REPLACE(LastNameAdjusted, ',', ' '), '.', '')
OPEN SuffixesCursor
FETCH FIRST FROM SuffixesCursor INTO @WhichSuffix
WHILE @@Fetch_Status = 0
BEGIN
EXEC ('Update #SuffixedName SET SuffixStartPosition = CHARINDEX(@WhichSuffix, LastNameAdjusted)')FETCH NEXT FROMSuffixesCursor INTO@WhichSuffix
END
CLOSE SuffixesCursor
SELECT*
FROM#SuffixedNames sn
ORDER BY
UID
DEALLOCATE SuffixesCursor
DROP TABLE #SuffixedNames
November 16, 2011 at 10:10 am
Cursors are bad, blah blah etc etc.
Now that's over with, can you just build the string dynamically? Like this -
FETCH FIRST FROM SuffixesCursor INTO @WhichSuffix
WHILE @@Fetch_Status = 0
BEGIN
EXEC ('Update #SuffixedName SET SuffixStartPosition = CHARINDEX('+@WhichSuffix+', LastNameAdjusted)') FETCH NEXT FROM SuffixesCursor INTO @WhichSuffix
END
November 16, 2011 at 10:16 am
That's exactly it. Thanks so much.
Mattie
November 16, 2011 at 11:54 am
MattieNH (11/16/2011)
I have a table of valid suffixes that I want to loop through (yes, a cursor).
You really don't. There is nothing in your query that indicates that a cursor is required—or dynamic SQL for that matter. This can easily be rewritten to use a single update statement.
You don't say what you want to do with names containing multiple suffixes, so I used the first one in the name.
UPDATE sn
SET LastNameAdjusted = Left(sn.LastName, ns.SuffixStartPosition - 1)
, SuffixStartPosition = ns.SuffixStartPosition
FROM #Suffixed_Name AS sn
CROSS APPLY (
SELECT TOP (1) Code, CharIndex(', ' + Code + ', ', LastName + ', ') AS SuffixStartPosition
FROM NameSuffixes
ORDER BY SuffixStartPosition
) AS ns
Drew
Edit to add a missing table alias.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 16, 2011 at 3:12 pm
CELKO (11/16/2011)
SQL is declarative, so we do this stuff in the DDL, not in fake mag tapes. The number of suffixes is constant and short, so put it in a CHECK() Constraint.
I would disagree with this statement. While the list of common suffixes is relatively short, you have to allow for ALL suffixes, not just the common ones. The list of all suffixes is by no means short. Also, while the list of suffixes is fairly stable, it is not constant. You can now get certified as a web developer, a job that didn't exist not too long ago.
Even something as seemingly simple as gender may not be as simple as it seems. Consider an organization that does genetic research. If they're doing cross-species studies a simple M/F dichotomy may not be enough. They may need to distinguish between F (XX), M(XY), F(WZ), and M(WW). They may also need to record certain kinds of trisomy such as XXX and XXY.
In a completely different situation, a client was working with some transexuals and needed to record male-to-female and female-to-male.
Putting these kinds of values in a table makes it much easier for the client to update the tables to meet their specific needs.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply