line by line meaning of this query

your welcome Susanta Rout

hello @Susanta I try to explain your stored procedure line by line.

Please consider

CREATE PROCEDURE GetCustomersPageWise

@PageIndex INT = 1

,@PageSize INT = 10

,@RecordCount INT OUTPUT

In this line you declare a stored procedure , in this stored procedure you pass three parameter. first two parameter(PageIndex , PageSize ) are Input type . this parameter are used to provide input in SP to perform sql query. Default value of PageIndex is 1 and default value of PageSize is 10. It means if you don't supply any value to PageIndex , PageSize parameter then sql server will use their default value. so can change these value according your requirement.

SET NOCOUNT ON;

this line is used to hide the message that show number of line effected

i means above query doesn't show such type of message "(9 row(s) affected)"

SELECT ROW_NUMBER() OVER

(

ORDER BY [CustomerID] ASC

)AS RowNumber

,[CustomerID]

,[CompanyName]

,[ContactName]

INTO #Results

FROM [Customers]

In this line of code create a temp(temporary) table . and insert value of [CustomerID]

,[CompanyName]

,[ContactName]

Field from Customeer table.

Important thing in this code you are creating one extra column that’s name is “RowNumber” this column doesn’t exist in Customer table. This column contain a integer value. Row_Number() method create a integer value and provide value according CustomerID column of Customer table.

You can understand such that suppose CustomerID column of Customer table contain following values

1

2

4

1

2

Then Row_Number function insert following value in RowNumber column of #Results table

1

2

3

4

5

SELECT @RecordCount = COUNT(*)

FROM #Results

In this line you insert total numbers of rows in @RecordCount Output variable.

SELECT * FROM #Results

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

In this code you select value from #Results table where value of RowNumber column is calculated by following code

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

Suppose value of PageIndex is 2 and PageSize is 10 then above query treat as follows

SELECT * FROM #Results

WHERE RowNumber BETWEEN 11 AND 20

DROP TABLE #Results

In this line you drop the temp table.

If we take an overall result then you will find two output result.

First one is provided by @RecordCount variable and second result is a table that is obtain by query

SELECT * FROM #Results

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