Emil's Wicked Cool Blog

I just posted a small utility on CodeProject to check for valid stored procedures, views, and functions (in SQL Server). It's actually a polish of some work someone else had done earlier, but if you have a large number of objects and are doing significant database refactoring, you may want to check it out.

A common pattern in applications is to have a search stored procedure work as the backend for a search screen. If a term has not been passed by the user, the intent is that there is no filter on the results for that parameter. Here's a great article discussing the ways that this can be acheived. I'm a personal fan of the COALESCE statement talked about in the static discussion.

One area that was not discussed in the article was the idea of having similar functionality with a list of filters (e.g. filter to these specific books in the library). I found a pretty good article talking about SQL 2005's use of Xml parameters to do this - Xml's main benefit being that it's more designed for this use case than a delimited string. However, the same problem now applies...what if I either have a list of books, or don't pass anything, meaning I want to search across all books?

My solution was to grab all the data from the reference table and build my own Xml string. Then I could use it in the main select statement:

IF @param is null
SET @param =
(SELECT 1 AS Tag,0 AS Parent,MyElementName AS [Root!1!string!element]
FROM sourcetable FOR XML EXPLICIT)

SELECT...
WHERE...
...AND
FieldName IN
(SELECT Field.value('.','VARCHAR(max)') FinalName
FROM @param.nodes('//string') AS Table(Field))

I'm sure this wouldn't perform well with large tables, but my reference tables are fairly small (<100 rows each), and it works great.

FYI: Here is an example COALESCE statement for the standard filters:

table LIKE '%' + COALESCE(@Name, fac.FacilityName ) + '%'

While SQL Server data update notifications through the SqlDependency object seemed like a great idea, I now believe the architecture is fundamentally flawed...I'm looking forward to changes in this area to make the feature more robust in the next release(s) of SQL Server and/or the .NET Framework. Here are some of the problems I've run into:

• Massive amounts of required permissions in the default run mode
• Query types available are incredibly restrictive
  
• Ineffective tear down of resources, again, in default run mode, with no programatic workaround. (The worst part? The problem is actually most acute while doing active development...)
  
• Severe ramifications for any issue, whether caused by environment or poor coding
  
• Complex (and maybe unusable) setup for custom service/queue implementation to alleviate the first two issues. I never really even figured out that setup when operating in a non-default schema in a restricted permission environment.

This feature is a great concept, but with an implementation that severely limits its use. My recommendation is to avoid this functionality in nearly all cases. In my current project, I've moved to a custom cache implementation that checks last modified date across all all lookup tables and clears the cache of all out of date lists. If something happens in between checks, we'll just have to deal with that.

While I like the idea of SQL 2005 Query notifications, the setup restrictions and instructions are fairly opaque. Blah! I did manage to get it working after noting all the restrictions on the query in this MSDN article, but then I made the mistake of removing dbo permissions from the user, and was thrown into the mix again for another hour of churning.

This blog post was pretty useful, but didn't go quite all the way. Later, I found a post describing more details after some searching, and came up with this set of grant statements to make it work:

• GRANT ALTER ON SCHEMA :: [schemaname] TO [Role]
• GRANT CREATE PROCEDURE TO [Role]
• GRANT CREATE QUEUE TO [Role]
• GRANT CREATE SERVICE TO [Role]
• GRANT REFERENCES on CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] to [Role]
• GRANT VIEW DEFINITION TO [Role]
• GRANT SELECT to [Role]
• GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [Role]
• GRANT RECEIVE ON QueryNotificationErrorsQueue TO [Role]
• GRANT IMPERSONATE ON USER::DBO TO [Role]