--因为你的表没有主键，无法区分重复的记录，先增加一个自增字段，用完后drop掉
ALTER TABLE t_ht_new ADD bh INT IDENTITY(1,1)
GO

--存储过程改为
CREATE PROCEDURE SP_TMP
AS
BEGIN
CREATE TABLE #TMP1
(
XH INT IDENTITY,
BH INT NULL
)


DECLARE @BH INT,@xiangmubh VARCHAR(20)
DECLARE C1 CURSOR FOR SELECT xiangmubh FROM t_ht_new GROUP BY xiangmubh HAVING COUNT(*)>1
OPEN C1
FETCH NEXT FROM C1 INTO @xiangmubh
WHILE @@FETCH_STATUS=0
BEGIN
TRUNCATE TABLE #TMP1
INSERT INTO #TMP1 (BH) SELECT BH FROM t_ht_new WHERE xiangmubh=@xiangmubh
UPDATE t_ht_new SET htnumber=rtrim(xiangmubh) + RIGHT('000'+CONVERT(VARCHAR(3),#TMP1.XH),3) FROM #TMP1 WHERE t_ht_new.BH=#TMP1.BH


FETCH NEXT FROM C1 INTO @xiangmubh
END
CLOSE C1
DEALLOCATE C1

END

--这是执行结果
A010314 aaa A010314001
A010314 aaa A010314002
A010314 aaa A010314003
A010314 aaa A010314004
A010314 aaa A010314005
A010314 aaa A010314006
A010314 aaa A010314007
A010314 aaa A010314008
A010314 aaa A010314009
A010314 aaa A010314010
A010314 aaa A010314011
A010314 aaa A010314012
A010314 aaa A010314013
A010314 aaa A010314014
A041105 CCC A041105001
A041105 CCC A041105002
A041131 BBB A041131001
A041131 BBB A041131002
A041131 BBB A041131003
A041131 BBB A041131004


--用完后drop掉新字段
ALTER TABLE t_ht_new DROP COLUMN bh