'Is it possible to call a user defined function inside SQL select statement

I am troubleshooting an issue with user defined function in SQL SELECT statement.

I am aware of the following syntax to access a UDF as part of SELECT query.

SELECT dbo.udf_function(param1) AS 'Output'

THE PROBLEM

But I have a situation where I need to blend above query as part of another SELECT statement. Something like below. At first place I know this is not possible as SQL gives error seeing below query as having a sub-query which is trying to return multiple columns (i.e. *)

SELECT 
    T1.Id, T1.Name, T1.Address, 
    (SELECT * FROM dbo.udf_function(param1)) AS 'Output'
FROM
    table_1 T1

This SQL is not working.

Is there any suggestion to handle above scenario?

sqlsql-serveruser-defined-functions


Solution 1:[1]

There are two scenarios :

(1) dbo.udf_function(param1) returns a scalar and produces one value for each record in table :

    SELECT 
    T1.Id
  , T1.Name
  , T1.Address
  , dbo.udf_function(param1) from table

(2) dbo.udf_function(param1) returns a table and produces rows for each record in table:

    SELECT 
    T1.Id
  , T1.Name
  , T1.Address
  , F.udf_field_1
  , F.udf_field_2 
   from table 
   cross apply dbo.udf_function(param1) F

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Xingzhou Liu

Related Questions