SAP HANA Table Functions: SQLScript Design, Examples, and Troubleshooting

A SAP HANA table function is a reusable SQLScript object that returns a tabular result. It is useful when a calculation requires procedural logic, input parameters, intermediate variables, or several SQL statements that are difficult to express in a single view definition.

The most reliable implementation starts with a precise output contract, a small test dataset, and an execution plan check. Keep the function focused on data derivation and let the consuming calculation view, application, or SQL statement handle presentation concerns.

01Understand the table function model

A table function has input parameters, a declared table return type, and an implementation body. A caller uses it in the FROM clause much like a table or view. The returned columns and their data types form the interface between the function and its consumers.

Table functions are generally read-only database objects. They are a good fit for set-based transformations, conditional logic, intermediate result sets, and reusable parameterized calculations. A procedure is a better fit when the operation needs output parameters, multiple result sets, transaction-oriented processing, or data changes.

For an architectural overview of the surrounding development topics, see SAP HANA development overview. For procedural SQLScript patterns, see SAP HANA SQLScript procedures.

Choose a table function when

• The consumer needs one tabular result with a stable column contract.
• The logic requires SQLScript variables, branching, or several intermediate queries.
• Input parameters determine the result set.
• The result will be consumed by SQL, a calculation view, or application code.

Choose a procedure when

• The operation changes data or coordinates a larger transactional workflow.
• Several result sets or scalar output parameters are required.
• The caller needs explicit procedural execution rather than a relational expression.

02Create a minimal SQLScript table function

Create the function in the target schema with the privileges required by your development workflow. The following example returns order totals for a supplied customer. Replace the source table and column names with objects that exist in the target schema.

CREATE FUNCTION "DEV"."order_totals"
(
    IN customer_id NVARCHAR(20)
)
RETURNS TABLE
(
    order_id NVARCHAR(20),
    total_amount DECIMAL(15,2)
)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
AS
BEGIN
    RETURN
        SELECT
            "ORDER_ID" AS order_id,
            SUM("NET_AMOUNT") AS total_amount
        FROM "DEV"."SALES_ORDER_ITEMS"
        WHERE "CUSTOMER_ID" = :customer_id
        GROUP BY "ORDER_ID";
END;

The RETURNS TABLE clause defines the public result shape. The colon before customer_id identifies the SQLScript variable reference inside the SQL statement. The RETURN statement supplies the final table expression.

Keep the declared output types compatible with the expressions in the final SELECT. Explicit casts can make the contract predictable when source columns have different numeric precision, lengths, or nullable behavior.

A function with several processing stages can assign intermediate result sets to table variables and return the final variable:

CREATE FUNCTION "DEV"."customer_order_summary"
(
    IN minimum_amount DECIMAL(15,2)
)
RETURNS TABLE
(
    customer_id NVARCHAR(20),
    order_count INTEGER,
    total_amount DECIMAL(15,2)
)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
AS
BEGIN
    filtered_orders =
        SELECT
            "CUSTOMER_ID",
            "ORDER_ID",
            "NET_AMOUNT"
        FROM "DEV"."SALES_ORDERS"
        WHERE "NET_AMOUNT" >= :minimum_amount;

    RETURN
        SELECT
            "CUSTOMER_ID" AS customer_id,
            COUNT(*) AS order_count,
            SUM("NET_AMOUNT") AS total_amount
        FROM :filtered_orders
        GROUP BY "CUSTOMER_ID";
END;

Use table variables to make meaningful processing stages visible. Avoid creating stages that only rename a column or add an unnecessary full data copy.

03Call and test a table function

Call a table function in a SELECT statement. Input parameters are supplied in parentheses, and the function is given an alias for convenient column references.

SELECT
    result.order_id,
    result.total_amount
FROM "DEV"."order_totals"(customer_id => 'C10001') AS result
ORDER BY result.order_id;

The exact parameter notation should match the SQL client and execution context in use. SAP HANA database explorer is useful for creating a focused SQL console test, while SAP HANA cockpit helps operators review the broader database context and monitoring information.

Test the function with representative values, an input that returns no rows, null-sensitive data, duplicate source rows, and boundary values for dates and amounts. Confirm both the data and the declared metadata. A query that returns plausible values can still expose an interface problem if a consumer expects a different column name or data type.

Use a small diagnostic query before testing the complete function when the implementation depends on joins or filters. This separates source-data problems from SQLScript problems and makes result differences easier to explain.

04Use table functions with calculation views

A table function can provide a scripted data source for a calculation view when the transformation cannot be expressed conveniently with graphical nodes. Define the output contract deliberately because downstream mappings depend on the function's column names and data types.

When the logic is a straightforward projection, join, aggregation, or filter, a graphical calculation view may be easier to maintain. Use a table function when procedural stages or parameter-driven logic provide a clear benefit. The comparison should include lineage, transport, testing, authorization, and runtime behavior rather than implementation preference alone.

For implementation choices between graphical and scripted models, see SAP HANA calculation views and SAP HANA graphical versus SQL calculation views.

Pass required parameters from the consuming model and verify how empty or null parameter values are handled. A function that assumes a parameter is always populated can silently produce an empty result or an unexpectedly broad result when called by a different consumer.

05Troubleshoot compilation and runtime errors

Start with the first reported error and validate the function in the same schema and connection context used by the consumer. Common causes include an incorrect object name, missing privileges, mismatched return columns, ambiguous column references, and an input parameter used without the SQLScript variable prefix.

Check the following in order:

1. Confirm that every referenced table, view, function, and column exists in the intended schema.
2. Check that the declared return column order, names, and data types match the final RETURN query.
3. Qualify columns with table aliases when joins contain similarly named columns.
4. Test each intermediate query independently when a table variable is involved.
5. Verify the caller's schema, object privileges, and SQL security behavior.
6. Re-run the function with a known input that should return a small result.

SQL SECURITY INVOKER evaluates access in the context of the calling user. Use the security model deliberately and grant only the object privileges required by the function and its consumers. Review authorization behavior from the same user that executes the application or calculation view.

A function that compiles but runs slowly needs execution analysis rather than repeated syntax changes. Check filter selectivity, join cardinality, aggregation volume, and whether a large intermediate table variable is being materialized. SAP HANA developer performance tuning provides related guidance for investigating expensive development objects.

06Improve performance and maintainability

Push selective filters toward the source query, project only required columns, and aggregate after reducing the input volume where the business logic permits. Avoid row-by-row loops for work that can be expressed as a set operation.

Use stable aliases and descriptive intermediate variable names. Keep business rules close to the query that applies them, but separate unrelated transformations into smaller functions or views when that improves testing and ownership.

Measure the function with realistic data volumes. A small development dataset can hide join multiplication, memory pressure, or a costly sort. Review the execution plan and expensive statements in the operational tools used for the SAP HANA system.

Parameter values can change the optimal execution path. Test both selective and nonselective inputs, especially when a function is called by a dashboard or application that allows broad date ranges or optional filters.

07Deploy changes safely

Treat the function signature as an interface. Renaming an output column, changing its type, or removing an input parameter can break calculation views, SQL statements, and application bindings. Coordinate interface changes with all known consumers.

Create the function in a development schema, test it with representative data, and transport the definition through the normal change process. Include dependent calculation views, grants, synonyms, and test cases when they are part of the deployment.

After deployment, run a smoke test using the same input and consumer path used in production. Check row counts, null behavior, authorization, and runtime. Keep the previous definition or a tested rollback package available when the change affects a heavily used data service.

08Resolve table function design problems

A function is easier to operate when its name describes the business result, its parameters have clear meanings, and its return contract is stable. Document assumptions such as time zones, currency units, default date ranges, and the treatment of missing values.

If a function has grown into a large workflow, divide it along meaningful data boundaries. If the logic is now a simple relational expression, move it to a view or calculation view where that improves transparency. If the operation needs data changes or several independent outputs, use a procedure instead.

The practical goal is a function that is predictable for callers, measurable at runtime, and straightforward to replace without changing every consumer.