one sql script for mssql 2000 and mssql 2005 (or later)

[Jono]

Is it possible to write a sql script that'll run some sql for 2000 if it's installed, or some other sql for 2005 or something else if version 7.. or something's installed - e.g. do nothing?

I can get the server version with @@VERSION, I think.

I can't seem to make it work in an if/else bit o' code.

What it's for:
For 2000 I'd do
--create some users:
EXEC sp_addlogin ...
EXEC sp_addlogin ...

but for 2005 I'd want to do :
create login ...
create login ...

At the moment I have 2 scripts - with only that bit different. - Can it be put into the same script so users only have to run one, without thinking (or knowing) which version of sql server they have installed?

 

[Jono]

Think i'll go for this sort of approach

IF CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(10)) LIKE '8.%'
	PRINT ' SQL Server 2000'
ELSE IF CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(10)) LIKE '9.%'
	PRINT ' SQL Server 2005'
ELSE IF CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(10)) LIKE '10.%'
	PRINT ' SQL Server 2008'

or

if @@version like '%Server 2005%'
begin
 exec
end
else
begin
 create
end

type thing

 

[Jono]

OH well it doesn't work

edit, yes it does.

One too many GO statements.