Programming for both SQL2000 and SQL7

[lawlerpat]

I would like to create a stored procedure which utilizes some user defined functions if the DB version is 2000 if not don't look for the UDF.
The following example displays what I would like to do, but does not work. It seems that SQL server will try to find the UDF even if it not actually accessed for processing.

The solution I am trying to avoid is writing two version of the SP or one very long version with the select code duplicated once for sql 2000 and once for sql7.
The SP is designed to return a dataset.

Create my_proc (@myHandle integer )
AS
Declare @serverVersion varchar(7)
Select @serverVersion = case when @@version like 'Microsoft Sql Server 2000%' then 'sql2000' else 'sql7'

Begin
Select
field1,
field2,
....
field55
Case @serverVersion when 'sql2000' then dbo.my_UDF(field2 esle '' end
From table1
end


Any thought of how to achieve the same thing differently?
Keeping in mind that the actual SP will have about 55 fields and be unioned.

Thanks

 

[philhege]

Since the UDF call is in a hard-coded query, the compiler will always check for its existence.

Try some dynamic SQL, in which you put the UDF reference in the SQL string if your condition is met, and then EXEC the SQL string variable. I think that approach should get around your problem.

HTH,
PH

 

[lawlerpat]

Thanks Phil,
Another option I had intended to avoid, but maybe my best option.

PL

 

[SQLSister]

Personally since you will have to write the long code anyway for the SQL7 version, why not just use it no matter whether the server is running SQL 2000 or SQL 7?
Then you don't have to use dynamic SQL. Sure it makes a longer SQL statement than if you used a UDF but SQL 7.0 doesn't Have UDF's so you have to write the long version anyway.