Converting string to int

Hi,

This probably is a basic question but I can't figure it out...

Because SQL Server's ISNUMERIC function allows some strange values to count as numeric (such as '\'), I want to create my own function that will only return an integer if the value is able to be converted (otherwise it will return 0). I have created the following function to do this:

CREATE FUNCTION [dbo].[fnConvertToInt]
(
@.str nvarchar(30)
)
RETURNS int
AS
BEGIN
DECLARE @.s int

BEGIN TRY
SET @.s = convert(int, @.str)
END TRY
BEGIN CATCH
SET @.s = 0
END CATCH
-- Return the result of the function
RETURN @.s
END

When I run this however, I get errors from having the TRY CATCH in a function:

Invalid use of side-effecting or time-dependent operator in 'BEGIN TRY' within a function.

How can I convert a string to an integer without getting an error? I am using SQL Server 2005.

Hi,

the isnumeric function is hazle, but you can implement some isreallynumeric, like aaron did this on his website:

http://www.aspfaq.com/show.asp?id=2390

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de