Retrieving Middle Rows from a Table

For retrieving the middle rows of a table irrespective of its columns needs to write a procedure which will take 3 parameters.

First parameter: The first parameter is the query of the particular table with respective column names (if needed or if u know the column details) otherwise u can give the * instead of column names with respective where condition in it, the parameter is of data type TEXT.

Second parameter: This parameter will take int value which is used to take values from the row where you want to start retrieving data.

Third parameter: This parameter also will take int value which is used to take values till which row you want retrieve data.

CREATE PROCEDURE sp_MidRows_Query(@Qry TEXT, @from INT, @to INT)
AS
BEGIN
DROP TABLE ##newTbl
DROP TABLE ##tbl
EXEC('SELECT * INTO ##newTbl FROM ('+@Qry+') temp')
SELECT * INTO ##tbl FROM ##newTbl
ALTER TABLE ##tbl ADD Sno INT IDENTITY(1,1)
SELECT * INTO #tbl FROM ##tbl
SELECT * FROM #tbl WHERE Sno BETWEEN @from AND @to
END

In this procedure Global variable table is used to store the data that is retrieved from the table.

Example for this procedure:

sp_MidRows_Query SELECT * FROM Employee WHERE Salary > 10000 , 4, 9

First parameter is SELECT * FROM Employee WHERE Salary > 10000

Second parameter is 4

Third parameter is 9

This will give the list of employees whose salary is greater than 10000 and also from 4th row to 9th row.


Similar Articles