------- SQL server Table value function ------&

SQL server Table value functions

Keywords: SQL server, table Value Function

The Table Value Function of SQL server Returns a table type, which is equivalent to a virtual Table stored in memory.
It is easy to implement the table value function:
The following is a table value function without input parameters.

Create function tvpoints ()
Returns table
As
Return
(
Select * from tb_users
);
This table value function queries all user table data.

For a multi-statement table-valued function, the function body defined in the BEGIN... END statement block contains a series of Transact-SQL statements. These statements can be generated in a row and inserted into the returned table.

The following example creates a table value function.

Create function tvpoints ()
Returns @ points table (x float, y float)
As begin
Insert @ points values (1, 2 );
Insert @ points values (3, 4 );
Return;
End

 

 

The function used to query table values is the same as that used to query common tables.
Select * from tvpoints ()
A table is returned.

 

 

Table-valued functions with input parameters

Create function tvpoints2 (@ x AS int, @ y as int)
Returns @ points table (x float, y float)
As begin
Insert @ points values (@ x, @ y );
Return;
End