Writing a SQL function that returns a function and get `Expecting EXTERNAL` near 'begin'

Question

The code tries to get all numeric columns and then returns the list as a new table but I am getting stuck at the begin part. The error is: Expecting EXTERNAL near 'begin'.

go
create function get_num_col(@table_name varchar(255))
returns table ( column_name varchar(255) )
as
begin
   select
      column_name
   from INFORMATION_SCHEMA.columns
   where TABLE_NAME = table_name
       and data_type in ('NUMBER', 'FLOAT', 'DOUBLE')
   return
end

Answer 1

The correct syntax you need for your function is:

create function get_num_col(@table_name sysname)
returns table 
as
return
   select column_name
   from INFORMATION_SCHEMA.columns
   where TABLE_NAME = @table_name and data_type in ('NUMERIC', 'FLOAT', 'REAL', 'DECIMAL', 'INT', 'SMALLINT', 'TINYINT');

Note the data type should be sysname which is a synonym for nvarchar(128)

Also note presumably you intended "numeric" and "decimal". See the sys.types table.

In addition, as mentioned in the comments, in SQL Server you should use sys.columns to determine the column properties where you can also use the type_name() function.

For example:

select t.[Name]
from sys.tables t 
where exists (
  select * from sys.columns c
  where t.object_id = c.object_id
    and Type_Name(c.system_type_id) in (
      'bigint',
      'decimal',
      'float',
      'int',
      'numeric',
      'real',
      'smallint',
      'tinyint'
   )
);