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:

  1. 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.
  2. 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

Popular posts from this blog

unicode - Are email addresses allowed to contain non-alphanumeric characters? -

c++ - Convert big endian to little endian when reading from a binary file -

C#: Application without a window or taskbar item (background app) that can still use Console.WriteLine() -