Table UDFs are read-only user-defined functions which accept multiple input parameters and return exactly one results table. SQLScript is the only language which is supported by table UDF's. Since these functions are read-only, only read-only statements can be used within the function. So, you may not use statements like INSERT, UPDATE or DELETE. Also, any procedure calls within the function must also be read-only. Currently, you can only create these functions in the catalog via the SQL Editor.
Syntax:
CREATE FUNCTION FUNCTION_NAME
(Parameter1 DATA_TYPE,Parameter2 DATA_TYPE)
RETURNS TABLE
( RetruenValue1 DATA_TYPE, ReturnValue2 DATA_TYPE )
BEGIN
RETURN SELECT 1* Parameter1 AS RetruenValue1 ,1*Parameter2 AS ReturnValue2 FROM mytab;
END;
Table User-defined Functions Support the Following:
v They can have any number of input parameters
v They return exactly one table
v Table operations are allowed within the body
v They are used in the FROM clause of SELECT statements
v They must be free of side-effects, i.e. DDL statements or the DML statements insert, update and delete cannot be used in function bodies
The SQL statement to define table user-defined functions also is create function. The difference to the definition of a scalar user-defined function is keyword table for the return parameter and how the value of the sole return parameter is assigned using keyword return:
Basic Syntax to Define a Table User-Defined Function
CREATE FUNCTION <function name> (<list of input parameters with type>) RETURNS TABLE [table type| (<list of table column definitions>)] AS BEGIN
<function body>
RETURN <expression to set return table>
END;
A feature that can be helpful in the context of defining table user-defined functions or stored procedures is dynamic filtering using built-in table function apply_filter. This function allows you to apply a dynamic WHERE clause to a database table or table variable and assign the result to a table variable.
Table:
E_ID
|
E_NAME
|
OVERTIME(in_Hrs)
|
1
|
Kabil
|
20
|
2
|
Nazeer
|
15
|
3
|
Kalai
|
10
|
4
|
Keerthi
|
10
|
CODE :
To Create a Function:
CREATE FUNCTION "KABIL_PRACTICE"."Convert_OfficialsHours"
(im_filter VARCHAR(1000), im_to VARCHAR(1) )
RETURNS TABLE ( "E_ID" NVARCHAR(3),
"E_NAME" NVARCHAR(20),
Overtime DEC (5,2))
AS BEGIN
lt_official = APPLY_FILTER("KABIL_PRACTICE"."OVERTIME"
,:im_filter);
RETURN SELECT "E_ID", "E_NAME",
"KABIL_PRACTICE"."Convert_Hours_IF_ELSE"("OVERTIME(in_Hrs)",:im_to) AS Overtime
FROM :lt_official;
END;
Note:
Apply_filter: You can use APPLY_FILTER with persistent tables and table variables.
The APPLY_FILTER function applies a dynamic filter on a table or table variable. Logically it can be considered a partial dynamic SQL statement. The advantage of the function is that you can assign it to a table variable and will not block SQL – inlining. Despite this, all other disadvantages of a full dynamic SQL yield also for the APPLY_FILTER.
"KABIL_PRACTICE"."Convert_Hours_IF_ELSE": It is a Scalar User Defined function which is already created.
To call a Function:
SELECT * FROM "KABIL_PRACTICE"."Convert_OfficialsHours"('E_NAME LIKE ''%K%''','d');
Result:
