Well, between watching college basketball and teaching and writing my next CoDe article, I've ignored my blog over the last month.
I have a community presentation that I've done over the last year and a half – it covers ASP.NET AJAX and SQL 2005. It's based on an actual client application that runs against a database with about a million rows.
Here's the "end result" . This is a bare-boned version, mainly intended to demonstrate fundamentals The client version, which I'm not allowed to show, is much more "pimped out" J
The presentation focuses on building a stored procedure in SQL 2005 that can handle the following:
- Searching a large database, based on full or partial text searches on Name, Address, City, etc.
- Searching on a variable # of statuses
- Paging the result set, and allowing the user to navigate back and forth (first ten, next ten, previous ten, last ten)
- Allowing user to specify the sort
- Allowing the user to quickly navigate to a letter, based on the current sort. (For instance, sorting on City and allowing someone to jump to the 'R's). If you think this isn't important – consider most web email clients…how many times have you had to click NEXT….NEXT…NEXT…NEXT to get to a certain area of the results???)
- Using ASP.NET AJAX, to implement partial page refreshes
OK, seems pretty simple? Well, it's amazing how a seemingly simple module can contain many fundamentals!
I've been in a debate with others about use of stored procedures for a task like this. I advocate stored procs as a starting point, unless there are critical reasons not to. Given that there are a good handful of variables here, some will choose the path of dynamic SQL, of constructing a SQL expression. Well, that's fine – however, it CAN be done with a stored procedure. Through the use of CASE statements, COALESCE statements to deal with NULL parameters, one can build a stored proc with the same flexibility. Bottom line: where possible, I'll always opt for creating a database API for tasks, and set up a basic test environment (which I'll show further down).
So here's the stored procedure. It receives the text search parameters, along with a startrowIndex (that's managed by the web application), the sort column to be used, as well as an optional "jump-to" character.
Also note there's an XML parameter, for the list of status codes. Sure, this particular situation only has a handful of status codes….but you could have other instances of variable selections where the list might be much larger (say a user selects 20 customers, or an even greater number of products). SQL 2005's XML capabilities are perfect for something like this – you can pass an XML string as a parameter, and shred it into a table variable back in SQL Server. (I've included a reusable UDF for this at the end, it's called XMLToTable, which you'll see used in the following stored proc):
ALTER PROCEDURE [dbo].[LookupEmployeeData]
@LastName varchar(50)=null, @FirstName varchar(50)=null,
@Address varchar(50)=null, @City varchar(50)=null,
@State varchar(2)=null, @Zip varchar(50)=null,
@StartRowIndex int, @MaxRows int,
@AlphaChar varchar(1)=null, @SortCol varchar(20)=null
@XMLStatuses XML
AS
BEGIN
-- StartRowIndex represents the first row to grab, based on current sort
-- MaxRows represents the max # of rows to return
-- alphaChar represents a "jump-to" character, based on current sort
-- (if an alphaChar is specified,
-- then the StartRowIndex is ignored
-- So someone might be sorting on City, and wants to jump straight to the 'R' cities
-- JumpChar is to give user an easier way than click NEXT…NEXT…NEXT…NEXT
-- All search parms are optional
SET NOCOUNT ON
DECLARE @lJumpToChar bit
-- If user did not specify a "jump-to" alphanumeric char, then
-- we're doing straight paging, based on the startrow Index
IF @AlphaChar is null
SET @lJumpToChar = 0
ELSE
-- If user DID specify a "jump-to" alphanumeric char, then
-- we're going straight to the first occurance of the alphachar,
-- based on the current sort
SET @lJumpToChar = 1
-- Query into CTE for 'base' results, and assign a row #, based on the ORDER
WITH CustListTemp AS
(SELECT CustomerID, LastName, FirstName, Address, City, State,
Zip, ROW_NUMBER() OVER (ORDER BY
CASE @SortCol
WHEN 'LASTNAME' THEN LastName + Firstname
WHEN 'ADDRESS' THEN Address
WHEN 'CITY' THEN City + LastName + Firstname
WHEN 'STATE' THEN STATE + LastName + Firstname
WHEN 'ZIP' THEN ZIP + LastName + Firstname
ELSE LastName + Firstname END)
AS RowNum
FROM Customers
-- Use the Table-valued UDF XMLToTable to shred an XML string
-- into a table variable, for querying against a set of values
JOIN dbo.XMLToTable(@XMLStatuses) StatList
ON StatList.IntPk = Customers.StatusFK
-- Check all the text searches…use COALESCE (or ISNULL), since some
-- could be NULL
WHERE LastName LIKE '%' + COALESCE(@LastName,LastName)+ '%' AND
FirstName LIKE '%' + COALESCE(@FirstName,FirstName) + '%' AND
Address LIKE '%' + COALESCE(@Address,Address) + '%' AND
City LIKE '%' + COALESCE(@City,City) + '%' AND
State LIKE '%' + COALESCE(@State,State) + '%' AND
Zip LIKE '%' + COALESCE(@Zip,Zip) + '%' )
-- Now query the CTE, for the TOP (N) rows,
-- based on either row number position (paging)
-- or the JumpChar
SELECT TOP (@MaxRows) CustomerID, LastName, FirstName,
Address, City, State, Zip, RowNum
FROM
( SELECT CustListTemp.*,
(SELECT COUNT(*) from CustListTemp) AS RecCount FROM CustListTemp ) CustList
WHERE
CASE
-- If the JumpChar is true, check the first character
-- of the sort order column against the AlphaChar
WHEN @lJumpToChar = 1 AND @SortCol= 'LASTNAME' AND
SUBSTRING(LastName,1,1) >= RTRIM(@AlphaChar) THEN 1
WHEN @lJumpToChar = 1 AND @SortCol= 'ADDRESS' AND
SUBSTRING(Address,1,1) >= RTRIM(@AlphaChar) THEN 1
WHEN @lJumpToChar = 1 AND @SortCol= 'CITY' AND
SUBSTRING(City,1,1) >= RTRIM(@AlphaChar) THEN 1
WHEN @lJumpToChar = 1 AND @SortCol= 'STATE' AND
SUBSTRING(State,1,1) >= RTRIM(@AlphaChar) THEN 1
WHEN @lJumpToChar = 1 AND @SortCol= 'ZIP' AND
SUBSTRING(Zip,1,1) >= RTRIM(@AlphaChar) THEN 1
-- If JumpChar is false, then check the row number
-- for the valid range
-- Note that if StartRowIndex is -1, that means user
-- wants the last page…in that case, count backwards from MaxRows
WHEN @lJumpToChar = 0 AND RowNum BETWEEN
(CASE @StartRowIndex
WHEN -1 THEN ( RecCount ) - @MaxRows
ELSE @StartRowIndex
END )
AND
( CASE @StartRowIndex
WHEN -1 then ( RecCount )- @MaxRows
ELSE @StartRowIndex
END) + @MaxRows
THEN 1
ELSE 0 END = 1
END
GO
And I promised the code for the reusable Table-valued UDF, to "shred" an XML string into a table variable (for subsequent joining). It uses the new XQUERY capabilities in 2005 to read nodes from an XML string. With this, I can handle any situation where a user passes a variable number of selections. Here it is:
ALTER FUNCTION [dbo].[XMLtoTable]
(@XMLString XML )
RETURNS
@tPKList TABLE ( IntPK int )
AS
BEGIN
INSERT INTO @tPKList
SELECT Tbl.col.value('.','int') as IntPK
FROM @XMLString.nodes('//IDpk' ) Tbl(col)
RETURN
END
So, that's the stored procedure. Now, I always like to test my stored proc inside SQL Management Studio – here's such a test.
So, that's it. Most people who have seen this, and/or have attended the presentation, like the idea. I know that some folks have taken the code and have implemented it for their own situations. Of course, some who eschew stored procedures will think I'm smoking crack. Hey, that's fine.
KG