Nested user-defined functions in tsql. Parameters passing trough INSERT-SELECT structures

Question

I have got two SQLServer2008 user-defined functions: fn_Top and fn_Nested. The first one, fn_Top, is structured in this way:

 CREATE FUNCTION [dbo].[fn_Top]
      RETURNS @Results table (
                        MyField1 nvarchar(2000),
                        MyField2 nvarchar(2000)
                       )
        AS    
        BEGIN  
        INSERT INTO 
                    @Results 
                    (
                    MyField1,
                    MyField2 
                    )
                    SELECT 
                        item,  
                        (SELECT MyString FROM dbo.fn_Nested(x.MyCounter))
                    FROM 
                    OtherTable x
       RETURN      
       END

I would like to perform a sort of dynamic parameter passing to the second function, fn_Nested, reading the values of the numeric field MyCounter in the table OtherTable.

Fact is, x.MyCounter is not recognized as a valid value. Everething works fine, on the other hand, if I set in fn_Nested a static parameter, IE dbo.fn_Nested(17).

Is there anyone who can suggest a workaround to solve this problem ? Thanks in advance.

Or, to be more clear, is dbo.fn_Nested a scalar or table-valued function? — mdisibio, Jul 04 '14 at 17:58

score 1 · Answer 1 · answered Jul 04 '14 at 18:24

Assuming fn_Nested is a table-valued function, your syntax is fine (although it could be simplified). The error would seem to indicate that either OtherTable does not have a column named MyCounter, or that it is not a column type that can be implicitly converted into numeric value. What is the exact error?

On a side note, you could simplify fn_Top by writing it as an inline table-valued function:

CREATE FUNCTION dbo.fn_Top()
RETURNS TABLE
AS
RETURN 
(  
        SELECT x,item as MyField1, y.MyString AS MyField2
          FROM dbo.OtherTable x
   CROSS APPLY dbo.fn_Nested(x.MyCounter) AS y
)
GO

score 0 · Answer 2 · edited May 23 '17 at 10:27

The syntax using the second function is described here

SQL User Defined Function Within Select

create table OtherTable (
item varchar(10),
MyCounter int
);

CREATE FUNCTION fn_Nested(@Id int)
RETURNS varchar(20)
AS
BEGIN
   RETURN 'testString ' + CAST(@Id AS VARCHAR(3))
END;

INSERT INTO OtherTable SELECT 'item1', 1
INSERT INTO OtherTable SELECT 'item2', 2

SELECT item,
dbo.fn_Nested(x.MyCounter) as MyString
FROM OtherTable x

Results:

item1 testString 1

item2 testString 2

Nested user-defined functions in tsql. Parameters passing trough INSERT-SELECT structures

2 Answers2