How to fix 'Parameters were not supplied for the function' error in SQL Server

SQL Server provides a feature called Table Valued Functions. These are user-defined functions that return a table data type and can be powerful alternatives to views.

Lets try to define a simple function of this kind using the following code:

CREATE FUNCTION X
(
    -- Add the parameters for the function here
    @a nvarchar(30), 
    @b nvarchar(30)
)
RETURNS 
@X TABLE 
(
    -- Add the column definitions for the TABLE variable here
    data nvarchar(250)
)
AS
BEGIN

    INSERT INTO X (data) 
    VALUES (@a + @b)    

    RETURN 
END
GO

The function just returns a table that contains the concatenation of two strings. We now try to use the function:

SELECT *
FROM X('Hello', 'World')

We expect to get a table that contains a single record but instead we get the following error message:

Parameters were not supplied for the function 'X'.

Click here for a live demo of the error.

The problem with our code is that it declares a table variable to be returned by the function that has the same name as the function. This is syntactically correct but then, instead of doing an INSERT into the table variable, it uses the function name, which happens to be the same.

What this type of, difficult to decipher, error teaches us is that it is much cleaner to use a different name for the table variable to be returned.

CREATE FUNCTION X
(
    -- Add the parameters for the function here
    @a nvarchar(30), 
    @b nvarchar(30)
)
RETURNS 
@output TABLE 
(
    -- Add the column definitions for the TABLE variable here
    data nvarchar(250)
)
AS
BEGIN

    INSERT INTO @output (data) 
    VALUES (@a + @b)    

    RETURN 
END
GO

Click here for a live demo.