Different procedures for SQL 2000 and 2005

I've got a stored procedure that now needs to be a little bit
different when my db has been installed on SQL 2000 vs SQL 2005. It's
created as part of the larger install script and my first thought was
that I keep a single install script and the script creates one or the
other version of the procedure depending on the SQL version that it's
being run on.
But when I try something like this:
if cast(serverproperty('productversion') as varchar(1)) = '8' --
8=2000 9=2005
create procedure xyz as
begin
set nocount on
-- SQL 2000 version
end
else
create procedure xyz as
begin
set nocount on
-- SQL 2005 version
end
Obviously it's not going to work; I get these errors from QA on SQL
2000, with pretty much the same from SSMS on 2005:
Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'procedure'.
Server: Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'else'.
Server: Msg 111, Level 15, State 1, Line 8
'CREATE PROCEDURE' must be the first statement in a query batch.
Can I do this? Or do I have to have two separate scripts, one for
2000 another for 2005 (and next year maybe a third script for 2008),
that are almost identical? Or use dynamic sql inside the if and else
to perform the creates (yuck) ? Or ...?
I've tried googling for how to handle this scenario, but no joy .
Can't believe that I'm the only one to encounter a multi-version
supporting need. How's this usually handled?
Thanks!
Hi Mark
You will need to make the procedure definition dynamic SQL
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[xyz]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[xyz]
IF LEFT(CAST(serverproperty('productversion') AS VARCHAR(128)),
CHARINDEX('.',CAST(serverproperty('productversion' ) AS VARCHAR(128)))-1) = '8'
BEGIN
EXEC ( '
create procedure xyz as
begin
set nocount on
-- SQL 2000 version
SELECT ''SQL 2000''
end
' )
END
ELSE
BEGIN
EXEC ( '
create procedure xyz as
begin
set nocount on
-- SQL 2005 version
SELECT ''SQL 2005''
end
' )
END
If you were using version control it would not be an issue to provide two
different sets of scripts and then you would only need to check the version
in the installer once.
John
"Mark Lemoine" wrote:

> I've got a stored procedure that now needs to be a little bit
> different when my db has been installed on SQL 2000 vs SQL 2005. It's
> created as part of the larger install script and my first thought was
> that I keep a single install script and the script creates one or the
> other version of the procedure depending on the SQL version that it's
> being run on.
> But when I try something like this:
> if cast(serverproperty('productversion') as varchar(1)) = '8' --
> 8=2000 9=2005
> create procedure xyz as
> begin
> set nocount on
> -- SQL 2000 version
> end
> else
> create procedure xyz as
> begin
> set nocount on
> -- SQL 2005 version
> end
> Obviously it's not going to work; I get these errors from QA on SQL
> 2000, with pretty much the same from SSMS on 2005:
> Server: Msg 156, Level 15, State 1, Line 2
> Incorrect syntax near the keyword 'procedure'.
> Server: Msg 156, Level 15, State 1, Line 7
> Incorrect syntax near the keyword 'else'.
> Server: Msg 111, Level 15, State 1, Line 8
> 'CREATE PROCEDURE' must be the first statement in a query batch.
> Can I do this? Or do I have to have two separate scripts, one for
> 2000 another for 2005 (and next year maybe a third script for 2008),
> that are almost identical? Or use dynamic sql inside the if and else
> to perform the creates (yuck) ? Or ...?
> I've tried googling for how to handle this scenario, but no joy .
> Can't believe that I'm the only one to encounter a multi-version
> supporting need. How's this usually handled?
> Thanks!
>