As a follow-up to my previous post on Observer Pattern View Filtering logic in SQL, I had to mention the potential sweetness of having access to the CLR in SQL2005. I've never seen SQL2005 except in presentations (I'm using .NET Beta 2.0 with SQL2000. No CTP-of-the-month for me, thanks!) Still, the idea of performing certain SQL tasks in managed code is very appealing.
To put as much smarts in the view filtering lookup data as possible I wanted to calculate and store the total number of jobs in SQL. It was in parsing out the IDs in column #3 that I really could have used a C# Split() method to quickly obtain the number of jobs listed in the comma-delimited list. I knew the commas were the key, so to speak, so I used the SQL Char() function in combination with Cursors. It works like a charm (until I can write managed code in SQL Server.) Code at bottom.
declare jobcountcursor CURSOR
GLOBAL
for select itemID, jobIDstring from views where viewtypeID = 0 order by itemID
open jobcountcursor
declare @jobIDstringchk varchar(1000)
select @totalcount = count(*) from Views where viewtypeID = 0
select @counter = 1
fetch next from jobcountcursor into @userIDchk, @jobIDstringchk
while (@counter <= @totalcount) and (@@fetch_status=0)
begin
select @string = @jobIDstringchk
WHILE @position <= DATALENGTH(@string)
BEGIN
if CHAR(ASCII(SUBSTRING(@string, @position, 1))) = ','
begin
set @count = @count + 1
end
SET @position = @position + 1
END
set @count = @count + 1
set @position = 1
update views set jobcount = @count where itemID = @userIDchk and viewtypeID = 0
set @count = 0
fetch next from jobcountcursor into @userIDchk, @jobIDstringchk
end
close jobcountcursor
deallocate jobcountcursor