Within the context of SQL Server, retrieving the server version is pretty easy, and there is a variety of ways one can go about retrieving the information.
You might consider calling the sp_server_info sproc that I mentioned in a post yesterday with specifying the attribute id of '2':
EXEC sp_server_info 2
Unfortunately, this returns columnar data so unless you do something like I proposed in said post it's largely unhelpful. Alternatively, and much more simply, you can query the @@version function (which I suspect ultimately defers to the same data that the sp_server_info sproc returns):
SELECT @@version
Still, this data may not be what you're looking for. It's quite verbose and contains a lot of extraneous information. You may be after just the version number. Fortunately, this is quite easily accomplished by querying the serverproperty function.
SELECT serverproperty('ProductVersion')
Incidentally, there is a lot more information you can glean from the server via the serverproperty function (product level, edition, instance name, and much more).
You may have need to write some TSQL that is conditional based on the target SQL Server edition. For instance, I'm in the process of writing software that must run on both SQL Server 2000 and SQL Server 2005, but where possible I'd like to use SQL Server 2005's capabilities and not necessarily cater to the least common denominator of SQL Server 2000.
The ProductVersion may still be too specific, as it contains minor versions, SP updates, etc. Essentially, what I want is to ensure that I'm dealing with the correct DB engine by major version # and not take into consideration any minor updates. The following code illustrates in a very simple manner extracting just the major version from the property. In this case, the TSQL simply returns a string indicating what was detected, but your code may actually contain logic:
DECLARE @ver nvarchar(128)
SET @ver = CAST(serverproperty('ProductVersion') AS nvarchar)
SET @ver = SUBSTRING(@ver, 1, CHARINDEX('.', @ver) - 1)
IF ( @ver = '8' )
SELECT 'SQL Server 2000'
ELSE IF ( @ver = '9' )
SELECT 'SQL Server 2005'
ELSE
SELECT 'Unsupported SQL Server Version'