MSSQL limit i offset

	B O A R D
	» Board » Zadaj pytanie » Archiwum » Szukaj » Stylizacja

M E N U

Szukaj @ TwojePC

	OBECNI NA TPC
	» NWN 05:07 » Shark20 02:49 » MARC 02:08 Dzisiaj przeczytano 41122 postów, wczoraj 25974 Szybkie ładowanie jest: włączone.

ccc

A R C H I W A L N A W I A D O M O Ś Ć

MSSQL limit i offset , AwerS 20/10/08 16:34
Witam

Mam pytanie jak mozna ustawic offset przy limit w sql dla mssql?

AwerS

Nie ma tej funkcjonalności (nie znam 2008) , Sherif 20/10/08 18:44
i trzeba kombinować z top.
ja to robilem procedurka , Holyboy 20/10/08 19:39
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [m].[get_rekordy] (
@wartosc1 INT,
@wartosc2 INT,
@pagenr INT = 1,
@offers_per_page INT = 20
)
AS

DECLARE @query VARCHAR(8000)

SET @query = '
SELECT
kol1, kol2, kol3, kol4, kol5
FROM (

SELECT TOP ' + CAST(@pagenr * (@offers_per_page) AS VARCHAR(6)) + '
ROW_NUMBER() OVER(ORDER BY id) AS TheCount,
kol1, kol2, kol3, kol4, kol5
FROM m.tmp o WITH (NOLOCK, NOWAIT)
WHERE o.kol1 = ' + CAST(@wartosc1 AS VARCHAR(6)) + '
AND o.kol2 = ' + CAST(@wartosc2 AS VARCHAR(6)) + '
ORDER BY id
) x WHERE x.thecount BETWEEN '
+ CAST((@pagenr - 1) * (@offers_per_page) + 1 AS VARCHAR(6))
+ ' AND ' + CAST(@pagenr * (@offers_per_page) AS VARCHAR(6))

EXEC(@query)
Strength is irrelevant.
Resistance is futile.
We wish to improve ourselves.