CREATE TABLE deed (deed_id INT IDENTITY(1,1), deed_name VARCHAR(25))
GO
INSERT INTO deed (deed_name) VALUES ('Deed F')
INSERT INTO deed (deed_name) VALUES ('Deed C')
INSERT INTO deed (deed_name) VALUES ('Deed I')
INSERT INTO deed (deed_name) VALUES ('Deed G')
INSERT INTO deed (deed_name) VALUES ('Deed B')
INSERT INTO deed (deed_name) VALUES ('Deed L')
INSERT INTO deed (deed_name) VALUES ('Deed D')
INSERT INTO deed (deed_name) VALUES ('Deed A')
INSERT INTO deed (deed_name) VALUES ('Deed J')
INSERT INTO deed (deed_name) VALUES ('Deed H')
INSERT INTO deed (deed_name) VALUES ('Deed K')
INSERT INTO deed (deed_name) VALUES ('Deed E')
GO
DECLARE @V_QUERY_STR VARCHAR(MAX),
@V_TABLE_NAME VARCHAR(50),
@V_START_ROW INT,
@V_QUERY_CNT INT,
@V_ORDER_BY_QUERY VARCHAR(50)
SELECT @V_TABLE_NAME = 'deed', @V_START_ROW = 1, @V_ORDER_BY_QUERY = 'ORDER BY deed_name'
SELECT @V_QUERY_STR = 'SELECT IDENTITY(INT, 1,1) as ROWINT, TN.* INTO #temp4 '
+ ' FROM ' + @V_TABLE_NAME + ' TN '
+ @V_ORDER_BY_QUERY
+ ' DECLARE @V_QUERY_CNT INT;'
+ ' SELECT @V_QUERY_CNT = COUNT(*) FROM ' + @V_TABLE_NAME + ';'
+ ' SELECT * FROM #temp4 WHERE ROWINT BETWEEN ' + CAST(@V_START_ROW AS VARCHAR) + ' AND ' + CAST(COALESCE(@V_QUERY_CNT, @V_START_ROW) AS VARCHAR)
EXECUTE (@V_QUERY_STR)
The result set I need would be:
1 Deed A
2 Deed B
3 Deed C
4 Deed D
...