In my last entry, I touched on a way to dynamically select a specific number of rows from a table in a SELECT SQL query. It works great if you are going to supply the value directly when you call the procedure. But what if you are using a static value that is stored in the database itself? I have a Settings table that stores Property/Value pairs, and one of those properties is the NumItems that I want to retrieve from the SELECT query!
I could create a public shared property that will retreive the value from the database, then pass that to the procedure call as a parameter, but that’s a lot of overhead, both in creating a new public property and then issuing two separate calls to the database. It would be a much better idea to inject the code to retrieve that value from within the stored procedure itself, since encapsulating the SELECT command to retrieve the latest entries (or whatever the case may be) logically makes more sense.
It took me a while to learn the correct syntax for decalring variables within SQL (it’s not my best language) but here’s the basic idea (notice I’m still using the SET ROWCOUNT method mentioned before):
DECLARE @NumItems int
SET @NumItems = (SELECT [Value] FROM cms_Settings WHERE CMSTable='News' AND [Property]='NumItems')
SET ROWCOUNT @NumItems
SELECT ID, Title, Icon, BodySummary, BodyExtended, DateEntered, Author, CategoryID, Lock, AllowComments, AllowRating
ORDER BY DateEntered DESC
-- Reset rowcount
SET ROWCOUNT 0
notice that this method couples the procedure directly to the intended table, preventing it from being used in a general method. But since this is a feature that is exclusively needed for the News table, I think the trade-off is worth the convenience.
As I was writing this, it occured to me that perhaps I could take advantage of the new My namespace in VB.NET to create a Settings entry, but I’ve never used that with a Web Application. I will investigate this strategy at a later time and if it works out I’ll report my findings in a new post.