Pagination-nøtt og dynamisk sql

[Kul drittunge]

Har lyst til å lage en fleksibel pagination-sak, men kommer ikke på noen annen måte å gjøre dette på enn ved dynamisk sql.

 

Her er da tanken å sende inn to tekststrenger til en prosedyre, splitte disse opp, og bygge opp order by-feltene ved hjelp av dem.

 

declare @order nvarchar (max)	
set @order = ''

select @order = 
@order 
+ case 
	when len (@order) > 0 then ', ' 
	else ' ' 
end 
+ sub.strOrder
from 
(
	select lub.string + ' ' + glub.string as strOrder
		, lub.listpos
	from 
		(
			select *
			from dbo.util_list2tbl2 ('n.nodeid,n.createddate')
		) as lub
		inner join 
		(
			select *
			from dbo.util_list2tbl2 ('desc,asc')
		) as glub on glub.listpos = lub.listpos				
) as sub
where 0 = 0
order by sub.listpos asc

select @order	

declare @sql nvarchar (max)
set @sql = 
'
	select top 1 n.nodeid
	from tblNode n
	order by
' + @order

select @sql

exec sp_executesql @sql

 

Her er da resultatene:

n.nodeid desc, n.createddate asc

 

select top 1 n.nodeid from tblNode n order by n.nodeid desc, n.createddate asc

 

Har ikke målt ytelse enda, men håper at den kan være grei.

Det jeg lurer på, og som jeg egentlig tror blir umulig, er om dynamisk order by kan implementeres uten dynamisk sql.

Endret 28. april 2008 av zY8pKPhR8XLJ

[kaffenils]

Det jeg lurer på, og som jeg egentlig tror blir umulig, er om dynamisk order by kan implementeres uten dynamisk sql.

 

Dynamisk SQL er nok den måten som kombinerer fleksibilitet og ytelse på en best mulig måte.

 

Du kunne selvfølgelig også skrevet en haug me IF tester og skrevet separate spørringer for hver mulig kombinasjon av kolonnene du kan sortere på, men de er jo ikke særlig fleksiblet. Du kan selvfølgelig ha en prosedyre som oppretter prosedyren som inneholder IF statementene og spørringene basert på f.eks. en tabell med lovlige sorteringskolonner.

[Kul drittunge]

Ja, havnet egentlig på den konklusjonen selv.

 

Her er da koden, om det er interessant:

/****** Object:  StoredProcedure [dbo].[getInFolder_advanced]	Script Date: 04/30/2008 19:58:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[getInFolder_advanced]
(
@uid int
, @parent_id int
, @nPerPage smallint = 100
, @nPage smallint = 1
, @customOrderByFields nvarchar (max) = null
, @customOrderByOrders nvarchar (max) = null
, @filterFields nvarchar (max) = null
, @filterFieldValues nvarchar (max) = null
, @debug bit = 'false'
) 
AS
BEGIN	
declare @sql nvarchar (max)
declare @defaultOrder nvarchar (max)	
declare @defaultOrderInverted nvarchar (max)	
declare @customOrder nvarchar (max)  
declare @customOrderInverted nvarchar (max)  
declare @filters nvarchar (max)
	set @filters = ''

	set @defaultOrder = 
		'
			case 
				when sortkey is not null then sortkey
				else 101
			end asc
			, createddate desc
		'	
	set @defaultOrderInverted = 
		'
			case 
				when sortkey is not null then sortkey
				else 101
			end desc
			, createddate asc
		'

select @customOrder =
		coalesce (@customOrder, '')
		+ case
			when len (@customOrder) > 0 then ', '
			else ' '
		end
		+ sub.strOrder
	, @customOrderInverted =
		coalesce (@customOrderInverted, '')
		+ case
			when len (@customOrderInverted) > 0 then ', '
			else ' '
		end
		+ sub.strOrderInverted
from
	(
		select lub.string + ' ' + glub.string as strOrder
			, lub.string + ' ' + 
				case 
					when glub.string = 'asc' then 'desc'
					else 'asc'
			end as strOrderInverted
			, lub.listpos
		from
			(
				select *
				from dbo.util_list2tbl2 (@customOrderByFields)
			) as lub
			inner join
			(
				select *
				from dbo.util_list2tbl2 (@customOrderByOrders)
			) as glub on glub.listpos = lub.listpos				
	) as sub
where 0 = 0
order by sub.listpos asc

select @filters =
		@filters
		+ ' and '
		+ sub.strFilter
from
	(
		select lub.string + ' ' + glub.string as strFilter 
			, lub.listpos
		from
			(
				select *
				from dbo.util_list2tbl2 (@filterFields)
			) as lub
			inner join
			(
				select *
				from dbo.util_list2tbl2 (@filterFieldValues)
			) as glub on glub.listpos = lub.listpos				
	) as sub
where 0 = 0
order by sub.listpos asc

if @debug = 'true' 
	select @customOrder as '@customOrder'
		, @customOrderInverted as '@customOrderInverted'
		, @filters as '@filters'

set @sql =
	'	
		select top (coalesce (@nPerPage, 10000)) *
		from
			(
				select top (coalesce (@nPerPage, 10000)) *
				from
					(
						select top ((coalesce (@nPerPage, 10000)) * @nPage) 
							f.nodeid
							, f.id
							, n.folder
							, f.parent_id
							, n.title
							, n.createdDate
							, coalesce (p.firstname + '' '' + p.lastname, ''unknown'') AS author
							, f.original as xpub 						
							, f.sortkey
							, n.onweb
						from tblNode n 
							INNER JOIN tblFamily f on f.nodeid = n.nodeid					
							LEFT OUTER JOIN tblPerson p on p.id = n.author
						where 0 = 0
							and f.parent_ID = @parent_id		
							and n.deleted = ''false'''
							+ @filters + '

						ORDER BY '
						+ coalesce (@customOrder, @defaultOrder)						
						+ '
					) as sub
					ORDER BY '
						+ coalesce (@customOrderInverted, @defaultOrderInverted)						
					+ '
			) as dub
			ORDER BY '
				+ coalesce (@customOrder, @defaultOrder)				
			+ '
	'

if @debug = 'true' select @sql as '@sql'

exec sp_executesql
	@stmt = @sql
	, @params = N'@nPerPage int, @nPage int, @parent_id int'
	, @nPerPage = @nPerPage
	, @nPage = @nPage
	, @parent_id = @parent_id		
end

 

Her er da util_list2tbl2 en funksjon som splitter en komma-separert liste av strenger.

Det hele ser ut til å kunne kjøre rimelig kjapt, om enn det ikke er helt optimalt da det er dynamisk SQL.

 

I alle tilfeller er jeg bestemt på at det er bedre enn å hente ut samtlige rader for så å sortere dem i applikasjonen.