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

Check the official documentation - it looks like you are mixing inline table valued function syntax with multi-statement table valued function syntax — Dale K, Aug 28 '23 at 07:31
This one for example https://learn.microsoft.com/en-us/sql/t-sql/statements/create-function-transact-sql?view=sql-server-ver16#b-creating-an-inline-table-valued-function — Nick.Mc, Aug 28 '23 at 07:33
Side note: `INFORMATION_SCHEMA` is for compatibility only, you should use `sys.columns` and `sys.types` instead. Also object and column names should be `sysname` which is the same as `nvarchar(128)` — Charlieface, Aug 28 '23 at 10:22

Stu · Accepted Answer · 2023-08-28T11:45:12.217

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'
   )
);

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

1 Answers1