SQL server query returns but function does not

Question

In my Java based web project, I have made one recursive query as below which runs perfectly fine and returns list of ids.

WITH treeResult(id) AS 
    (SELECT pt.id FROM myschema.art_artwork_tree AS pt WHERE pt.id in 
      (select node_id from myschema.art_brand_user_mapping where emp_id = $1)
     UNION ALL 
     SELECT pa.id FROM treeResult AS p, myschema.art_artwork_tree AS pa 
     WHERE pa.parent_node = p.id and pa.site_id = $2) SELECT id FROM treeResult AS n
    );

Now, I want to use it in JPQL query. So, I have made function as below.

USE [darshandb]
GO

DROP FUNCTION [dbo].[testfunction]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

    CREATE FUNCTION [dbo].[testfunction] (@empId INT,@siteId INT)
     RETURNS TABLE
     WITH SCHEMABINDING
     AS
     RETURN
     (
      WITH treeResult(id) AS 
        (SELECT pt.id FROM myschema.art_artwork_tree AS pt WHERE pt.id in 
          (select node_id from myschema.art_brand_user_mapping where emp_id = $1)
         UNION ALL 
         SELECT pa.id FROM treeResult AS p, myschema.art_artwork_tree AS pa 
         WHERE pa.parent_node = p.id and pa.site_id = $2) SELECT id FROM treeResult AS n
    );
GO

When I am trying to execute function, it does not return any value.

SELECT * FROM [dbo].[testfunction] (4,3);

Please help me, what I have done wrong.

Answer 1

I think your problem is the use of $1 and $2 in your function query. Just use the original parameter names in your table valued function.

So, replace $1 by @empId and $2 by @siteId in your user defined function.