Must declare the table variable error while creating a TVP function

Question

I have the following function created with a TVP. I am inserting data initially into the TVP and then selecting from it.

CREATE FUNCTION [dbo].employees_data(
  @employeeIds NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
-- WITH SCHEMABINDING
AS
BEGIN

  CREATE TYPE employees_data
  AS TABLE (can_view_all BIT,role_id INT ,employee_id INT);

  DECLARE @EmployeeDataTVP AS employees_data;

  INSERT INTO @EmployeeDataTVP (can_view_all, role_id, employee_id)
  SELECT
      ur.role_id,
      ua.employee_id
      INNER JOIN user_roles ur ON ur.user_account_id = ua.id
      WHERE ua.employee_id IN (SELECT * FROM STRING_SPLIT(@employeeIds, ','));


   RETURN (SELECT
    (
      CASE
      WHEN ed.role_id = 1 THEN (
        SELECT
          cc.id,
          ISNULL(cc.standard_name, cc.localized_name) AS name,
          cc.client_cost_center_id AS cost_center_id
        FROM s eccs
        LEFT JOIN centers cc on eccs.center_id = cc.id
        WHERE eccs.employee_id = ed.employee_id
        FOR JSON PATH
      )
      ELSE NULL
    END
    ) AS managed_places,
    ed.employee_id
  FROM @EmployeeDataTVP ed
   )
END

But i get the Must declare the table variable \"@EmployeeDataTVP\". error when i execute the function. I cannot figure out what i am doing wrong.

Where is the table-valued parameter? You should be using one instead of a comma-delimited list of values. — HABO
– HABO, Commented Nov 16, 2019 at 15:34

gotqn · Accepted Answer · 2019-11-16 04:50:41Z

1

You do not need to create type but table variable instead:

CREATE FUNCTION [dbo].employees_data(
  @employeeIds NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
-- WITH SCHEMABINDING
AS
BEGIN

  DECLARE @EmployeeDataTVP TABLE (can_view_all BIT,role_id INT ,employee_id INT);


  INSERT INTO @EmployeeDataTVP (can_view_all, role_id, employee_id)
  SELECT
      ur.role_id,
      ua.employee_id
      INNER JOIN user_roles ur ON ur.user_account_id = ua.id
      WHERE ua.employee_id IN (SELECT * FROM STRING_SPLIT(@employeeIds, ','));


   RETURN (SELECT
    (
      CASE
      WHEN ed.role_id = 1 THEN (
        SELECT
          cc.id,
          ISNULL(cc.standard_name, cc.localized_name) AS name,
          cc.client_cost_center_id AS cost_center_id
        FROM s eccs
        LEFT JOIN centers cc on eccs.center_id = cc.id
        WHERE eccs.employee_id = ed.employee_id
        FOR JSON PATH
      )
      ELSE NULL
    END
    ) AS managed_places,
    ed.employee_id
  FROM @EmployeeDataTVP ed
   )
END

answered Nov 16, 2019 at 4:50

gotqn

44k47 gold badges167 silver badges257 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Sagar Acharya Over a year ago

Thanks it worked. But i get Only one expression can be specified in the select list when the subquery is not introduced with EXISTS error i believe it is because of (SELECT * FROM STRING_SPLIT(@employeeIds, ',') statement. Is there anything i can do to remove the error?

gotqn Over a year ago

Try to change it with SELECT [value] FROM STRING_SPLIT(@employeeIds, ',')

Thom A Over a year ago

Why use a multi-line TVF? This looks like it could easily be an inline TVF, which would perform way better.

Sagar Acharya Over a year ago

How would a inline tvf work. Is there any documentation for it?

Collectives™ on Stack Overflow

Must declare the table variable error while creating a TVP function

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related