model name更改为不允许重复, 原来重复的model name则需要提供sql script处理(可加后缀"_"區分)

在SQL server里时不区分大小写的

ItemID     ModelName      Status --------想整成这样------ItemID       ModelName     Status

1               marytest           testing                                        1        marytest                    testing     

2               MaryTest          testing                                        2        MaryTest _1              testing

3               mm                   testing                                        3        mm                             testing

4               MARYTEST      testing                                        4       MARYTEST_2           testing

SO 我写了一个存储过程来执行

游标跳过第一行，对第二行进行处理。故用了两次FETCH NEXT

CREATE PROCEDURE UpdateBMSITEMNAME
AS
BEGIN
DECLARE @item_id   int
DECLARE @item_name nvarchar(500)
DECLARE @qty       int
DECLARE @no        int
DECLARE main_cursor CURSOR FOR
		SELECT MODELNAME,COUNT(*) AS QTY FROM BMSItem GROUP BY MODELNAME HAVING COUNT(*)>1
OPEN main_cursor
FETCH NEXT FROM main_cursor INTO @item_name,@qty
WHILE ((@@FETCH_STATUS) =0)
BEGIN
	SET @no=0
	DECLARE line_cursor CURSOR FOR
		SELECT ITEMID FROM BMSITEM WHERE MODELNAME=@item_name
	OPEN line_cursor
	FETCH NEXT FROM line_cursor INTO @item_id
	FETCH NEXT FROM line_cursor INTO @item_id
	WHILE ((@@FETCH_STATUS)=0)
	BEGIN
		print 'item_id'
		print @item_id
		set @no = @no + 1
		UPDATE BMSITEM SET MODELNAME=MODELNAME+'_'+CONVERT(NVARCHAR(50),@no) WHERE ITEMID=@item_id
		FETCH NEXT FROM line_cursor INTO @item_id
	END
	CLOSE line_cursor
	DEALLOCATE line_cursor

	FETCH NEXT FROM main_cursor INTO @item_name,@qty
END
CLOSE main_cursor
DEALLOCATE main_cursor
END