ProductId	FunctionName	   
1	dbo.SampleFunction1	   
2	dbo.SampleFunction2

--- Set up the sample functions

CREATE FUNCTION SampleFunction1
	(@ProductId INT) returns
            @Info TABLE
				(ProductId INT,
				TestColumn VARCHAR (30))
      AS
      BEGIN
            INSERT
            INTO  @Info
            SELECT 1, '1 - It works!' RETURN
      END


CREATE FUNCTION SampleFunction2
	(@ProductId INT) returns
            @Info TABLE
				(ProductId INT,
				TestColumn VARCHAR (30))
      AS
      BEGIN
            INSERT
            INTO  @Info
            SELECT 2, '2 - It works!' RETURN
      END

--- Set up the Lookup table 

DECLARE @Lookup TABLE 
	(ProductId INT, 
	FunctionName VARCHAR(30))
      INSERT
      INTO  @Lookup
      SELECT 1, 'dbo.SampleFunction1' UNION ALL
      SELECT 2, 'dbo.SampleFunction2' 

--- Find the function that goes with the given ProductId (ProdId)

DECLARE @ProdId INT
SET @ProdId = 1

DECLARE @FunctionName VARCHAR(30)
SET @FunctionName =
      (SELECT lkp.FunctionName
      FROM  @lookup lkp
      WHERE lkp.ProductId = @ProdId)

-- Here, I would like to call the function using @FunctionName, but can't figure out if it's even possible to do that.
-- If this were a stored procedure, I could just use EXEC @ProcedureName. 

-----This is the sort of thing I'm looking for, where this type of code would call the function:
-----SELECT *
-----FROM (@FunctionName) 

-- Drop the sample functions

DROP FUNCTION SampleFunction1
DROP FUNCTION SampleFunction2

EXEC('SELECT * FROM ' + @FunctionName + '(parameters here)')