SQL服务器查询返回,但功能不
问题描述:
在我的基于Java
的Web项目,我做了一个recursive
查询如下,它运行得很好,并返回列表ids
。SQL服务器查询返回,但功能不
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
);
现在,我想用它在JPQL
查询。所以,我做了如下function
。
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
当我试图执行function
时,它不返回任何值。
SELECT * FROM [dbo].[testfunction] (4,3);
请帮助我,我做错了什么。
答
我认为你的问题是在你的函数查询中使用了$1
和$2
。只需使用表值函数中的原始参数名称即可。
因此,在您的用户定义函数中,用@empId替换$ 1并用@siteId替换$ 2。