CREATE PROC Pro_NaviGation
(
@PageIndex int,
@PageSize int,
@NaviName varchar(20)
)
AS
DECLARE @PageSql varchar(2000)

BEGIN TRY
SET  @PageSql='SELECT  TOP ' + CAST(@PageSize as varchar)
SET  @PageSql +=' * FROM (
SELECT ROW_NUMBER() OVER (ORDER BY NaviId) ROWID,
* FROM NaviGation '
IF (@NaviName!='')
SET @PageSql+=' WHERE NaviName LIKE ''%'+@NaviName+''''
SET @PageSql+=') K '
SET @PageSql+='  WHERE ROWID >'+CAST(((@PageIndex - 1)*@PageSize + 1) AS VARCHAR)
SET @PageSql+=' SELECT COUNT(*) FROM  NaviGation'
IF (@NaviName!='')
SET @PageSql+=' WHERE NaviName LIKE ''%'+@NaviName+''''

PRINT(@PageSql)
EXEC(@PageSql)

END TRY 
BEGIN CATCH
	DECLARE @MSG varchar(500)=error_message()
	PRINT @MSG
END CATCH