To implement a "what's new" module view for a series of modules I'm writing for a client, I have several Sql Server 2005 sprocs that use "SELECT TOP (@MaxRows) . . . " where @MaxRows is passed as in in parameter. Although the module will be deployed for this client in a site that uses SQL Server 2005, I will be making a public release of the module later with a good chance that it might be used on a site that makes use of SQL Server 2000 which does not support use of a variable in this case.

I can either 1) indicate to potential users that he module requires SQL Server 2005 rather than 2000 or 2) rewrite the sprocs to use either dynamic SQL or the ROWCOUNT property or 3) check the value of @@Version in the sproc to then branch to the appropriate code for either SQL Server 2005 or 2000 versions.  I hate to do #2 and not use the new capability of SQL Server 2005 - primarily for performance reasons as the "what's new" module will appear on the home page of the client's site. Most likely, I'll take the extra time to go with #3 for the greatest compatiblity.

What are other developers doing to handle differences between SQL Server 2005 and 2000?

How many people do you think will need the SQL 2000 version? I would simply have another version that has SQL 2000 script and see how many people requested it. I think it would be a small percentage. Not enough to lose the performance gains.

Have you used the Ranking Functions yet? Once you have used those you just can't believed you were ever able to live without them :)