Monday, March 19, 2012

How to get Specific rows from Table

hi

i m using row count in order to get first 16 rows from a specific table...now i want to get rows from row no. 16 to 32 (or any no which i want)...can any one tell meee how can i query it using sql server 200

HI,

do it as

for n to n1 records do as

select top n1-n * from

(

select top n1 * from

)

order by key desc

|||

If your total result set is small enough (> 1000 records) you could insert into a table variable with an identity column and then select back out referencing the identity column in the where clause. For example:

DECLARE @.tblTable TABLE

(

TableID INT IDENTITY(1,1)

,OtherID INT

,Value VARCHAR(50)

)

INSERT @.tblTable (OtherID, Value) VALUES (1, 'One')

INSERT @.tblTable (OtherID, Value) VALUES (2, 'Two')

INSERT @.tblTable (OtherID, Value) VALUES (3, 'Three')

INSERT @.tblTable (OtherID, Value) VALUES (4, 'Four')

INSERT @.tblTable (OtherID, Value) VALUES (5, 'Five')

DECLARE @.Start INT, @.End INT

SELECT @.Start = 1, @.End = 3

SELECT *

FROM @.tblTable

WHERE TableID BETWEEN @.Start AND @.End

|||

It seems that you are trying to do pagination in database.

Below is the code snippet for a stored procedure. This takes page number and numbers of records in a Page.

CREATE PROCEDURE Pagination
@.Page int,
@.Size int
AS

DECLARE @.Start int, @.End int
BEGIN TRANSACTION GetDataSet
SET @.Start = (((@.Page - 1) * @.Size) + 1)
IF @.@.ERROR <> 0
GOTO ErrorHandler
SET @.End = (@.Start + @.Size - 1)
IF @.@.ERROR <> 0
GOTO ErrorHandler
CREATE TABLE #TemporaryTable
(
Row int IDENTITY(1,1) PRIMARY KEY,
Project varchar(100),
Buyer int,
Bidder int,
AverageBid money
)
IF @.@.ERROR <> 0
GOTO ErrorHandler
INSERT INTO #TemporaryTable
SELECT ...
-- Any kind of select statement is possible with however many joins
-- as long as the data selected can fit into the temporary table.
IF @.@.ERROR <> 0
GOTO ErrorHandler
SELECT Project, Buyer, Bidder, AverageBid
FROM #TemporaryTable
WHERE (Row >= @.Start) AND (Row <= @.End)
IF @.@.ERROR <> 0
GOTO ErrorHandler
DROP TABLE #TemporaryTable
COMMIT TRANSACTION GetDataSet
RETURN 0
ErrorHandler:
ROLLBACK TRANSACTION GetDataSet
RETURN @.@.ERROR

Regards

Sachin

No comments:

Post a Comment