SQL
UPDATE: Added support for importing tracked forums and topics.
I'm in the process of converting my forums from Community Server to AspNetForum. While CS may support every single feature under the sun, its very big and unwieldly to work with. The more recent 2007 and 2008 versions are better, but still behemoth. I just want to drop in one simple forum with a couple subgroups, not host a million forums and blogs on my site. For this, AspNetForum fits the bill exactly.
There's no official importer for Community Server to AspNetForum, so I wrote my own. It turns out to be fairly...
Since I recently posted a Split UDF, I figured I'd do some research on the best way to do a Join operation. Unfortunately, UDF's can't take table variables as input parameters, so I had to just write a snippet of straight sql. Here's the solution:
DECLARE @valueList varchar(1000)
SELECT
@valueList = COALESCE(@valueList + ',', '') + CAST(Field AS varchar)
FROM
Table
The only way I'd seen before to do this operation involved a cursor, so it was great to come across this idea. Basically, the use of COALESCE function makes this work. For the first row, it returns an empty string, as @valueList is initially null....
I recently had a friend ask for help comparing two comma seperated lists in SQL server using a UDF. I started based around the Split UDF I wrote, and continued from there. The simplest and most performant thing I could think of was to use an INNER JOIN to compare the table values of the two lists. Then just compare the counts you get back to make sure they're all the same. Here's the UDF:
SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO
CREATE FUNCTION IsCommaListMatch( @list1 varchar(255), @list2 varchar(255))RETURNS bitASBEGIN DECLARE @isMatch bit
DECLARE @list1Values TABLE (val int) DECLARE @list2Values TABLE (val int)
INSERT INTO @list1Values SELECT * FROM dbo.Split(@list1) INSERT INTO...
I've seen a lot of people on the net looking for a SQL split user defined function. Looking around, there didn't seem to be anything solid and performant. I found this article from 4guysfromrolla, but looking at all those function calls makes me sick. So I wrote my own hopefully performant UDF. I removed the split character parameter, as almost all uses I saw were based on commas. But that should be very simple to add in.
Let me know if you think of any other ideas to make it faster or better.
SET
ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE
FUNCTION Split
(
@list varchar(255)
)
RETURNS @listTable...