Step by Step 101-OrderNumber re-ordering under the same pcode

Use [K2_workflow_test]go/** * * * object:storedprocedure [dbo].    [Sp_updatebpm_dictionaryforordernumber] Script DATE:2018/1/4 16:08:46 * * * * **/SET ansi_nulls ongoset quoted_identifier ongoalter procedure [dbo]. [Sp_updatebpm_dictionaryforordernumber][Email protected]intOutput--number of rows affected asbegin-- oncatch Exception beginTry--Create a temporary table for storing"Dictionary Table"The ordernumber repetition in the parent code (PCODE) IF object_id ('tempdb.dbo. #devicetemp','U') is not a NULL DROP TABLE dbo. #devicetemp; SELECT pcode into dbo. #devicetemp fromBpm_dictionary_test--Note that the test table will be tested first, and will be converted to formal form Group by Pcode,ordernumber have count (pcode)>1--declare variable DECLARE @Pcode nvarchar ( -) while EXISTS (select Pcode from dbo. #devicetemp) BEGIN SELECT @Pcode=pcode from dbo. #devicetemp; --Create a temporary table for storing"Dictionary Table"The details of the current parent encoding (PCODE) repeat IF object_id ('tempdb.dbo. #repeatDetailInfotemp','U') is not a NULL DROP TABLE dbo. #repeatDetailInfotemp;                 BEGIN TRANSACTION SELECT Id,ordernumber,pcode into dbo. #repeatDetailInfotemp From Bpm_dictionary_testwherePcode=@Pcode ORDER by ordernumber desc; --declare the variable DECLARE @Id as INT, @OrderNumberint                    Set@OrderNumber =0While EXISTS (SELECT Id from dbo. #repeatDetailInfotemp) BEGIN SELECT @Id=Id from dbo. #repeatDetailInfotemp; UPDATE bpm_dictionary_testSet[Email protected]whereId =@Id; Set@OrderNumber = @OrderNumber +1; SET ROWCOUNT0DELETE from dbo. #repeatDetailInfotemp WHERE Id=@Id; END COMMIT TRANSACTION SET ROWCOUNT0DELETE from dbo. #devicetemp WHERE pcode=@Pcode; END--Set@row =@ @rowcount EndTryBEGINCatchROLLBACK TRANSACTION--Set@IsSuccess ='failed!'ENDCatchEnd

Stored Procedures

Step by Step 101-OrderNumber re-ordering under the same pcode