Column value of an SQL result is the name of a UDF that needs to be executed and returned in the result set

Question

I want to query my database and return a table.

• The table I have contains a column with the names of user defined functions.
• I want to execute the value of the UDF column and return its result as a column in the result set of the query.

TblContainers

ID	UID	ContainerType	Size	UDF
1	XXX	Chest	HUGE	udf_GetChestCount
2	YYY	Tube	SMALL	udf_GetTubeCount
3	XXX	Box	TINY	udf_GetBoxCount

And the result set I want should look like:

ID	ContainerType	Size	Container COUNT
1	Chest	HUGE	13 (count obtained by calling udf_GetChestCount)
3	Box	TINY	11 (count obtained by calling udf_GetBoxCount)

Each row will have its own UDF to call in order to obtain a container count. I am unsure how to do that last part.

I was trying to do something like the following (with @uid = 'XXX ')

SELECT ID, ContainerType, Size, (EXEC(UDF)(@uid) AS [ContainerCount]
FROM TblContainers WHERE UID=@uid

But it doesn't work for me. How would I construct a query that will return what I need?

Answer 1

I ended up using a CASE statement, and direct function calls as suggested by Peter Mith and Stu. Thanks all.

CREATE FUNCTION [dbo].[udf_GetContainerCounts](@uid VARCHAR(30))    
    DECLARE @v_ID numeric;

    SET @v_UID = 'XXX'; 
    SELECT ID, ContainerType, Size, UID,
    CASE
       WHEN ContainerType = 'CHEST' THEN (call UDF)
       WHEN ContainerType = 'TUBE' THEN (call UDF)
       WHEN ContainerType = 'BOX' THEN (call UDF)               
       ELSE 0
    END AS [ContainerCount]
    FROM TBLContainers;