— Calculate number of rows to delete for each grouping by subtracting
— 1 from the total count for a given group.
SELECT @iCount = @iCount – 1
SELECT @chCount = CONVERT(char(3),@iCount)
— now build the rowcount and delete statements.
SELECT @nvchCommand = N’SET ROWCOUNT ‘ + @chCount +
‘DELETE IndTest ‘ +
‘ WHERE vchFirstName = ‘ + CHAR(34) + @vchFirstName + CHAR(34) +
‘ AND vchLastName = ‘ + CHAR (34) + @vchLastName + CHAR(34) +
‘ AND vchAddress1 = ‘ + CHAR(34) + @vchAddress1 + CHAR(34)
— print the statement. For your viewing pleasure only.
PRINT @nvchCommand
— execute the statement.
EXEC sp_executesql @nvchCommand
SELECT @iErrorVar = @@Error
IF @iErrorVar <> 0
BEGIN
RETURN
END
FETCH NEXT FROM DelDupe INTO @iCount,
@vchFirstName,
@vchLastName,
@vchAddress1
END
CLOSE DelDupe
DEALLOCATE DelDupe
RETURN
About the Author
David VanDeSompele has worked directly with database systems for 11 years. He is currently the senior DBA for a Seattle-based company that provides firewall and VPN solutions.]]>