SqlException:fix the sql exception

Question

ALTER PROCEDURE [dbo].[GetCategoriesPageWise]

@PageIndex INT = 1

,@PageSize INT = 10

,@RecordCount INT OUTPUT

AS

BEGIN

SET NOCOUNT ON;

 

 

SELECT ROW_NUMBER() OVER

(

   ORDER BY [Id] ASC

)   AS RowNumber

,[id]

,[code]

,[name]

,parent_id

INTO #Results

FROM [lwpos_categories]

 

SELECT @RecordCount = COUNT(*)

FROM #Results

SELECT --[id]

[code] as 'Category Code'

,[name] as 'Category Name'

, (select p.parent_id from lwpos_categories p inner join lwpos_categories p1 on p.parent_id=p1.id) as 'Parent Name'

FROM #Results

WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1

 

DROP TABLE #Results

END

 ================================

 

 

 

 

 

 

 

int PageSize = 5;

int recordCount;

private void loadDataGridView(int pageIndex)

{

//Ref:https://www.aspsnippets.com/Articles/Implement-Paging-DataGridView-in-Windows-Forms-WinForms-Application-using-C-and-VBNet.aspx

string cs = System.Configuration.ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;

SqlConnection con = new SqlConnection(cs);

SqlCommand cmd = new SqlCommand("GetCategoriesPageWise", con);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.AddWithValue("@PageIndex", pageIndex);

cmd.Parameters.AddWithValue("@PageSize", PageSize);

cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4);

cmd.Parameters["@RecordCount"].Direction = ParameterDirection.Output;

con.Open();

DataTable dt = new DataTable();

CategoryGV.DataSource = dt;

con.Close();

recordCount = Convert.ToInt32(cmd.Parameters["@RecordCount"].Value);

this.PopulatePager(recordCount, pageIndex);

}