Those Community Server Super Devs can sure whip up an amazing query parameter. These dynamically created queries are passed to stored procedures like in our example, to cs_Threads_GetThreadSet. There's an example of a query passed as a stored proc parameter in an earlier Queensryche post.
The query shown in the CS 2.1 post above was kid's stuff compared to the query passed to CS2007's cs_Threads_GetThreadSet proc. The code you see below is an example of a CS2007 query. The main overhaul (probably implemented in pre-CS2007 builds on supersized deployments) was how SectionID testing is handled. Remember the past query logic?
and SectionID in (5,23,93,24,154,44,92.....)
This could extend into the hundreds (or more!) and seriously gum-up the works. I've bolded the highlights below of how this approach has been overhauled in CS2007, which is seriously slick I should add.
SET Transaction Isolation Level Read UNCOMMITTED
DECLARE @iXml int
DECLARE @sectionIDs table (sectionID int not null primary key)
DECLARE @excludeSectionIDs table (sectionID int not null primary key)
EXECUTE [master].[dbo].[sp_xml_preparedocument] @iXml OUTPUT, N'<xml><SectionIDs><i>3</i><i>4</i></SectionIDs></xml>'
set nocount on
insert into @sectionIDs SELECT a.sectionID FROM
OPENXML(@iXml, '/xml/SectionIDs/i', 2) WITH ( sectionID int '.' ) AS a
set nocount off
EXECUTE [master].[dbo].[sp_xml_removedocument] @iXml
Select P.PostID From dbo.cs_Posts P right join dbo.cs_Threads t on
(P.ThreadID = T.ThreadID) inner join dbo.cs_Sections S on S.SectionID = P.SectionID
where P.SettingsID = 1000 and S.SectionID in ( select sx.sectionID from @sectionIDs sx )
and S.IsActive = 1 and P.ApplicationPostType & 1 <> 0 and
Day(P.UserTime) = 19 and Month(P.UserTime) = 4 and Year(P.UserTime) = 2007
Order by P.PostDate asc
[Queensryche]