I have below proc: for updating display order:

CREATE PROCEDURE proc_Disporder

@ID INT,

@OldDisplayOrder INT,

@NewDisplayOrder INT

AS

BEGIN

IF(@OldDisplayOrder >= @NewDisplayOrder) OR (@OldDisplayOrder = 0)

BEGIN

UPDATE miscitem

SET DisplayOrder = DisplayOrder + (CASE WHEN @OldDisplayOrder = @NewDisplayOrder THEN 1 ELSE 2 END)

FROM MiscItems miscitem

WHERE ID <> @ID

AND miscitem.DisplayOrder >= @NewDisplayOrder

AND miscitem.DisplayOrder > 0

END

IF(@OldDisplayOrder < @NewDisplayOrder) AND(@OldDisplayOrder <> 0)

BEGIN

UPDATE miscitem

SET DisplayOrder = (CASE (DisplayOrder - 1) WHEN 0 THEN DisplayOrder ELSE (DisplayOrder - 1) END)

FROM MiscItems miscitem

WHERE ID <> @ID

AND miscitem.DisplayOrder <= @NewDisplayOrder AND miscitem.DisplayOrder >= @NewDisplayOrder - 1

AND miscitem.DisplayOrder > 0

END

DECLARE @MiscItems TABLE(DisplayOrder INT IDENTITY(1,1),ID INT)

INSERT @MiscItems

SELECT ID FROM MiscItems WHERE DisplayOrder > 0ORDER BY DisplayOrder

UPDATE src

SET src.DisplayOrder = mi.DisplayOrder

FROM MiscItems src

INNER JOIN @MiscItems mi ON src.ID = mi.ID

END

one of other scenario is failing.. plz help..