Syntaxes of Creating Table-Valued Functions in SQL Server

Q

How Many Ways to Create Table-Valued Functions in SQL Server Transact-SQL?

✍: FYIcenter.com

A

SQL Server supports two syntaxes of creating table-valued functions:

1. Inline Table-valued Functions - A table-valued function created with a single SELECT statement: 

CREATE FUNCTION function_name(
   @parameter_1 data_type, 
   @parameter_2 data_type, 
   ...
   @parameter_n data_type
   )
   RETURNS TABLE
   AS 
   RETURN (select_statement);

2. Multi-statement Table-valued Functions - A table-valued function created with a local temporary table and a statement block: 

CREATE FUNCTION function_name(
      @parameter_1 data_type, 
      @parameter_2 data_type, 
      ...
      @parameter_n data_type
      )
   RETURNS @table_variable_name TABLE (
      column_definition_list)
   AS BEGIN
      statement_1;
      statement_2;
      ...
      statement_n;
      RETURN
   END

 

⇒Using User Defined Functions in SQL Server Transact-SQL

⇒⇒SQL Server Transact-SQL Tutorials

2016-12-18, 248👍, 0💬

General MySQL Oracle SQL Server