Select Query: Retrieve Fixed Number of Rows Part 2
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):
BEGIN
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
FROM cms_News
ORDER BY DateEntered DESC
-- Reset rowcount
SET ROWCOUNT 0
END
GO
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 occurred 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.
Enjoyed this post and/or found it useful?
Tagged with:
SelAromDotNet
Josh loves all things Microsoft and Windows, and develops solutions for Web, Desktop and Mobile using the .NET Framework, Azure, UWP and everything else in the Microsoft Stack.
His other passion is music, and in his spare time Josh spins and produces electronic music under the name DJ SelArom.
