SQL what is wrong with this syntax? BEGIN...IF...RETURN

Question

Below is a excerpt from a function. I keep getting a syntax error

Incorrect syntax near the keyword 'RETURN'

My code:

CREATE FUNCTION dbo.convttxt (@mhtztxt VARCHAR(MAX))
RETURNS FLOAT
AS
BEGIN
    DECLARE @mhtz FLOAT 

    IF (ISNUMERIC(LTRIM(RTRIM(@mhtztxt)))
        RETURN CONVERT(FLOAT, LTRIM(RTRIM(@mhtztxt))) 

    SET @mhtz = (SELECT 
                     REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE([COL1], 'xyz', 0)              
              ,'<Enter Text Here>', 0)
              ,'<Enter Text Here>', 0)
              ,'<Enter Text Here>', 0)
              ,'<Enter Text Here>', 0)
              ,'<Enter Text Here>', 0)
              ,'<Enter Text Here>', 0)
              ,'<Enter Text Here>', 0) 

                 FROM dbo.table1)

    RETURN @mhtz
END

Answer 1

This is fixed (but @tgr beat me to it).

ALTER FUNCTION dbo.convttxt (@mhtztxt VARCHAR(MAX))
RETURNS FLOAT AS
BEGIN
  DECLARE @mhtz FLOAT;
  --below: extra parenthesis removed, variable spelled wrong, ISNUMERIC statement was not complete

  IF ISNUMERIC(ltrim(rtrim(@mhtztxt)))=1 
  --RETURN convert(FLOAT, ltrim(rtrim(@mhtztxt))) 
    SET @mhtz = convert(FLOAT, ltrim(rtrim(@mhtztxt)));
  ELSE
  SET @mhtz= 
    (SELECT TOP(1)
        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE([COL1] 
                ,'xyz', 0)              
                ,'<Enter Text Here>', 0)
                ,'<Enter Text Here>', 0)
                ,'<Enter Text Here>', 0)
                ,'<Enter Text Here>', 0)
                ,'<Enter Text Here>', 0)
                ,'<Enter Text Here>', 0)
                ,'<Enter Text Here>', 0) 
                from (SELECT 1.00 UNION ALL SELECT 2.00) table1(col1)); -- emulating your table

   RETURN @mhtz;
END -- needed an End

You can further simplify this like so:

ALTER FUNCTION dbo.convttxt (@mhtztxt VARCHAR(MAX))
RETURNS FLOAT AS
BEGIN
  RETURN
    CASE 
      WHEN ISNUMERIC(ltrim(rtrim(@mhtztxt)))=1 
      THEN convert(FLOAT, ltrim(rtrim(@mhtztxt)))
      ELSE
      (SELECT TOP(1)
          REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE([COL1] 
                  ,'xyz', 0),'<Enter Text Here>', 0),'<Enter Text Here>', 0)
                  ,'<Enter Text Here>', 0),'<Enter Text Here>', 0),'<Enter Text Here>', 0)
                  ,'<Enter Text Here>', 0),'<Enter Text Here>', 0) 
                  from (SELECT 1.00 UNION ALL SELECT 2.00) table1(col1)) -- emulating your table
    END
END

Answer 2

but the replace logic is still missing ;-)

CREATE FUNCTION dbo.convttxt (@mhtztxt VARCHAR(MAX))
RETURNS FLOAT
AS
BEGIN
    DECLARE @mhtz FLOAT 

    IF ISNUMERIC(LTRIM(RTRIM(@mhtztxt))) = 1 
       SET @mhtz = CONVERT(FLOAT, LTRIM(RTRIM(@mhtztxt))) 
    ELSE
    SELECT @mhtz = 
                     REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(name, 'xyz', 0)              
              ,'<Enter Text Here>', 0)
              ,'<Enter Text Here>', 0)
              ,'<Enter Text Here>', 0)
              ,'<Enter Text Here>', 0)
              ,'<Enter Text Here>', 0)
              ,'<Enter Text Here>', 0)
              ,'<Enter Text Here>', 0) 

                 FROM sys.all_objects
    ;

    RETURN @mhtz
END