Retrieve the version of SQL Server from within a user defined function

EXEC master.dbo.xp_msver ProductVersion can be used to return the server version in a resultset. I need this to do some conditional coding between varchar and varchar(max) in a UDF, so size of the text I return must be different between the SQL2000 and SQL2005.

I cant call an xp_ that returns a resultset within a UDF can I, so how can I get the SQL version?

have you tried

select serverproperty('ProductVersion') as character_value

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

You can use @.@.Version within a user defined function. I tested the following code in both SQL 2000 and SQL 2005. Hope this helps.

Alter Function dbo.VersionNumber()

Returns int

As

Begin

Declare @.Temp VarChar(1000)

Declare @.Output Int

Select @.Temp = @.@.Version

Set @.Temp = Replace(Left(@.Temp, CharIndex('-', @.Temp)-1), 'Microsoft SQL Server', '')

If IsNumeric(@.Temp) =1

Set @.Output = Convert(int, @.Temp)

Else

Set @.Output = 0

Return @.Output

End

go

Select dbo.VersionNumber()

|||

serverproperty works very nicely, thanks.

I had tried @.@.VERSION, which worked, but not pleasant. Now changed to serverproperty