SQL服务器查询返回但功能不 [英] SQL server query returns but function does not

问题描述

在我的基于Java 的web项目中，我已经完成了一个递归查询，列表 ids 。

  WITH treeResult（id）AS 
（SELECT pt.id FROM myschema.art_artwork_tree AS pt WHERE pt.id in 
（从myschema.art_brand_user_mapping中选择node_id，其中emp_id = $ 1）
 UNION ALL 
 SELECT pa.id FROM treeResult AS p，myschema.art_artwork_tree AS pa 
 WHERE pa.parent_node = p.id和pa.site_id = $ 2）SELECT id FROM treeResult AS n 
）; 
  

现在，我想在 JPQL 查询。所以，我已经做了函数如下。

  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 
  

当我试图执行函数，它不返回任何值。

pre code> SELECT * FROM [dbo]。[testfunction]（4,3 ）;


请帮助我，我做错了什么。

解决方案

我认为你的问题是使用 $ 1 和 $ 2 在你的函数查询中。只需在表值函数中使用原始参数名称即可。

因此，在您的用户定义函数中，用@empId替换$ 1并用@siteId替换$ 2。

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.

解决方案

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.

这篇关于SQL服务器查询返回但功能不的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！