Software Blog: SQL

One of my biggest annoyances about SQL Server is the inability to load a database from a later version into a previous one. Database files and backups don't have any indicators of their version (unless you are OCD enough to enforce naming conventions) so whenever I get a backup file from a client (or from my archive) it's a crapshoot which server it belongs to.

Worst of all, restoring it to a LATER version IS supported, so if I have an SQL Server 2005 backup and restore it to my local SQL Server 2008, it works fine. Then when I ...

Up until now I've been blowing off adding paging support to my web projects. This is very bad practice and I figured it was time to stop. Now I know enough SQL to make a SELECT command (if I have a program that can generate it for me :P) so I didn't even know where to BEGIN with this one.

Thankfully, the knowlegable folks over at 4GuysFromRolla.com have already done the work for me and gave detailed steps in this handy tutorial that made creating the paging stored procedure a SNAP!

Thanks to their detailed tutorial I was able to ...

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

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