Change Display Order in Table using Sql

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Project : VirSanghvi
-- Procedure Name : [usp_Admin_DisplayOrder_ChangeDisplayOrder]
-- Author: Rajat
-- Create date: 17-Sep 2008
-- Description: updating the Forum Post
-- Modified By :
-- Modification Date:
-- =============================================


--usp_Admin_DisplayOrder_ChangeDisplayOrder 'Tbl_Article','ArticleID',1,3,''
-- select * from Tbl_ForumPost

ALTER PROCEDURE [dbo].[usp_Admin_DisplayOrder_ChangeDisplayOrder]
@TableName varchar(100),
@FieldName varchar(100),
@ID varchar(10),
@CurrentDisplayOrder varchar(10),
@Level varchar(100)

AS
DECLARE @RetVal INT,@strSql varchar(5000),@strSqlNew varchar(5000),@strSqlNew1 varchar(5000),@oDisplayOrder varchar(50)
SET @RetVal = -1
SET @strSql =''
SET @strSqlNew=''
SET @strSqlNew1=''
BEGIN TRY
--BEGIN TRAN
CREATE TABLE #TempDisplayOrder (
DisplayOrder varchar(50) )
IF(@Level <> '')
BEGIN
SET @strSql = @strSql + ' SELECT DisplayOrder FROM '+@TableName+' WHERE '+@FieldName+'='+@ID + ' and Level='+@Level
END
ELSE
BEGIN
SET @strSql = @strSql + ' SELECT DisplayOrder FROM '+@TableName+' WHERE '+@FieldName+'='+@ID
END

INSERT INTO #TempDisplayOrder(DisplayOrder)
EXEC(@strSql)
SELECT @oDisplayOrder=DisplayOrder FROM #TempDisplayOrder
--PRINT @oDisplayOrder
IF(@Level <> '')
BEGIN
SET @strSqlNew =@strSqlNew+' Update '+ @TableName +' Set DisplayOrder='+@CurrentDisplayOrder+' WHERE '+@FieldName+'='+@ID

IF(@CurrentDisplayOrder > @oDisplayOrder)
BEGIN
SET @oDisplayOrder = @oDisplayOrder + 1
SET @strSqlNew1 =@strSqlNew1+' Update '+ @TableName +' Set DisplayOrder=DisplayOrder - 1 WHERE Level='+@Level+' and DisplayOrder Between '+@oDisplayOrder +' and '+@CurrentDisplayOrder +' and '+ @FieldName+'<>'+@ID
END
ELSE
BEGIN
SET @oDisplayOrder = @oDisplayOrder - 1
SET @strSqlNew1 =@strSqlNew1+' Update '+ @TableName +' Set DisplayOrder=DisplayOrder + 1 WHERE Level='+@Level+' and DisplayOrder Between '+@CurrentDisplayOrder +' and '+@oDisplayOrder +' and '+ @FieldName+'<>'+@ID
END
END
ELSE
BEGIN
SET @strSqlNew =@strSqlNew+' Update '+ @TableName +' Set DisplayOrder='+@CurrentDisplayOrder+' WHERE '+@FieldName+'='+@ID

IF(@CurrentDisplayOrder > @oDisplayOrder)
BEGIN
SET @oDisplayOrder = @oDisplayOrder + 1
SET @strSqlNew1 =@strSqlNew1+' Update '+ @TableName +' Set DisplayOrder=DisplayOrder - 1 WHERE DisplayOrder Between '+@oDisplayOrder +' and '+@CurrentDisplayOrder +' and '+ @FieldName+'<>'+@ID
END
ELSE
BEGIN
SET @oDisplayOrder = @oDisplayOrder - 1
SET @strSqlNew1 =@strSqlNew1+' Update '+ @TableName +' Set DisplayOrder=DisplayOrder + 1 WHERE DisplayOrder Between '+@CurrentDisplayOrder +' and '+@oDisplayOrder +' and '+ @FieldName+'<>'+@ID
END
END
--COMMIT TRAN
--RETURN @RetVal
--PRINT @CurrentDisplayOrder

--PRINT @strSqlNew
--PRINT @strSqlNew1
EXEC(@strSqlNew)
EXEC(@strSqlNew1)
END TRY

BEGIN CATCH
--ROLLBACK TRAN
--SET @RetVal = -1
--RETURN @RetVal
END CATCH