tsql - How do I convert the following code to a SQL Server/T-SQL CTE? -
i consider myself rather proficient t-sql , i'm able optimize query pretty without loosing readability. in short: sql short, descriptive, declarative , elegant.
while following code works, have 2 problems it:
- i using cursors , can't shake feeling have in of head have been done more efficiently using ctes. cursors don't work in views, can't manipulate results/ranges on client-side or in dependent sql.
- the code implemented in stored procedure, leads same problem above. linq sql , auto-paging.
so given following sp, see obvious way convert plain select using recursive ctes? i've tried, failed , thought i'd see stack overflow community might able come with.
set ansi_nulls on go set quoted_identifier on go create proc [dbo].[usp_getlastreferers] ( @limit int = null ) begin set nocount on create table #referer ( id int, url nvarchar(500), referer nvarchar(500) ) declare @id int declare @url nvarchar(500) declare @referer nvarchar(500) declare @count int set @count = 0 declare logcursor cursor forward_only read_only select id, url, referer log referer <> '' order id desc open logcursor fetch next logcursor @id, @url, @referer while @@fetch_status = 0 , (@count < @limit or @limit null) begin declare @hits int select @hits = count(*) #referer referer = @referer declare @islocal bit select @islocal = dbo.islocalsite(@referer) if (@hits = 0 or @hits null) , @islocal = 0 begin insert #referer(id,url,referer) values (@id,@url,@referer) set @count = @count + 1 end fetch next logcursor @id, @url, @referer end close logcursor deallocate logcursor select * #referer drop table #referer set nocount off end
since may not totally obvious, i'm trying here akin tothe following quasi sql
select distinct top(@limit) id, url, referer log order id desc
basically last unique refers (not unique rows), contain duplicates, , in descending order. gets tricky.
the data pretty simple http logs. id field unique row-identifier, url full url requesten, , referer http referer request. none of values can null, referer can empty (ie ''). issitelocal simple filtering function exclude referers originating own sites.
if wants sample-data full around, can upload small db-backup have fool around with.
sample-data can found here: http://svada.kjonigsen.net/files/iislogsdbbackup.zip
why convert recursive cte ? there no reason can't run plain select.
i downloaded test database, missing dbo.islocalsite function, test created own same named function , assumed returned 0.
this code when run produced exact same results stored procedure given above:
select top (@limit) id, url, referer ( select id, url, referer, rank() on (partition referer order id desc) _rank_ log dbo.islocalsite(referer) = 0 , referer != '' ) tt _rank_ = 1 order id desc;
Comments
Post a Comment