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 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.

-SelArom

Select Query: Retrieve Fixed Number of Rows

As I’m beginning work on my senior project, I have discovered the need to select a specific number of rows from a SELECT query in SQL Server 2000. Although the new 2005 version has the built in ability to pass a variable for the TOP clause, no such functionality exists in SQL SERVER 2000. Fortunately I found a great resource showing various ways to accomplish it. The best one in my opinion is to pass the variable into the SET ROWCOUNT command, which can take a variable as a value:

 


CREATE PROCEDURE dbo.getFoo
@top INT
AS
BEGIN
SET ROWCOUNT @top

SELECT foo
FROM blat
ORDER BY foo DESC

-- never forget to set it back to 0!
SET ROWCOUNT 0
END
GO

 

this preserves the security your stored procedure (vs building it as a string) and keeps things relatively tidy. It’s a shame it took them this long to add a built-in mechanism into SQL SERVER but I’m just glad there’s always a workaround 🙂

-SelArom