天翼翔幻

因為有一些資料需要進行更新，想說架構並不大，就去研究一下 Cursor 來撰寫

當然那 IF 只是我想額外看的，要不然其實他可以寫在最前面的 Select 內

DECLARE @cnt int, @id bigint, @aid int, @SerialNO nvarchar(16), @Abbreviation nvarchar(6), @tmpstr nvarchar(16) SET @cnt = 0 DECLARE my_cursor CURSOR FOR SELECT id, aid, SerialNO FROM table1 OPEN my_cursor Fetch NEXT FROM my_cursor INTO @id, @aid, @SerialNO WHILE @@FETCH_STATUS = 0 BEGIN IF @SerialNO IS NULL BEGIN SET @cnt = @cnt + 1 SET @tmpstr = 'NOSR-' + RIGHT('00' + CAST(@cnt AS nvarchar(3)), 3) update table1 SET SerialNO = @tmpstr where id=@id Fetch NEXT FROM my_cursor INTO @id, @aid, @SerialNO END ELSE BEGIN print CAST(@cnt AS nvarchar(4)) + '|' + @SerialNO Fetch NEXT FROM my_cursor INTO @id, @aid, @SerialNO END END CLOSE my_cursor DEALLOCATE my_cursor