I am trying to write a function to support some (legacy) SQL code, function will be used as a part of a query:
我正在尝试编写一个函数来支持一些(遗留)SQL代码,函数将被用作查询的一部分:
SELECT
q.*,
fn_qncatxml(q.number, q.version) AS categories --XML aggregated category hierarchy
FROM
[dbo].[qn] q
Unfortunately following approach does not work as SQL Server has some known problems with CTE expressions in a function (semicolon before WITH solution). Unfortunately I'm not sure how to apply this solution to a function returning scalar, like this:
不幸的是,下面的方法不起作用,因为SQL Server在函数中有一些已知的CTE表达式问题(WITH解决方案之前的分号)。不幸的是,我不确定如何将此解决方案应用于返回标量的函数,如下所示:
CREATE FUNCTION [dbo].[fn_qncatxml](@qnnumber INT, @qnversion INT)
RETURNS XML
WITH RETURNS NULL ON NULL INPUT
BEGIN
RETURN
(
WITH [categories] AS
(
SELECT [qn].[number], [qn].[version], [cat].[catnumber], [qncat].[itemnumber], [cat].[parent], [cat].[description], 0 AS [distance]
FROM [qn]
LEFT JOIN [qncat] ON [qncat].[qnnumber] = [qn].[number] AND [qncat].[qnversion] = [qn].[version]
LEFT JOIN [cat] ON [cat].[catnumber] = [qncat].[catnumber] AND [cat].[status] = '1'
WHERE [qn].[number] = @qnnumber AND [qn].[version] = @qnversion
UNION ALL
SELECT [categories].[number], [categories].[version], [cat].[catnumber], [categories].[itemnumber], [cat].[parent], [cat].[description], [categories].[distance] + 1 AS [distance]
FROM [categories]
JOIN [cat] ON [cat].[catnumber] = [categories].[parent]
WHERE
[cat].[status] = '1'
)
SELECT DISTINCT * FROM [categories] FOR XML PATH('')
)
END
When I'll strip a part between WITH and FOR XML PATH(''), give some valid arguments and execute this block, it works perfectly.
当我将在WITH和FOR XML PATH('')之间删除一个部分时,给出一些有效的参数并执行这个块,它完美地工作。
Has anyone a clue how to make this CTE function syntax work in my scenario ?
有谁知道如何使这个CTE函数语法在我的场景中工作?
1 个解决方案
#1
3
CREATE FUNCTION [dbo].[fn_qncatxml](@qnnumber INT, @qnversion INT)
RETURNS XML
WITH RETURNS NULL ON NULL INPUT
BEGIN
DECLARE @Ret xml;
WITH [categories] AS
(
SELECT [qn].[number], [qn].[version], [cat].[catnumber], [qncat].[itemnumber], [cat].[parent], [cat].[description], 0 AS [distance]
FROM [qn]
LEFT JOIN [qncat] ON [qncat].[qnnumber] = [qn].[number] AND [qncat].[qnversion] = [qn].[version]
LEFT JOIN [cat] ON [cat].[catnumber] = [qncat].[catnumber] AND [cat].[status] = '1'
WHERE [qn].[number] = @qnnumber AND [qn].[version] = @qnversion
UNION ALL
SELECT [categories].[number], [categories].[version], [cat].[catnumber], [categories].[itemnumber], [cat].[parent], [cat].[description], [categories].[distance] + 1 AS [distance]
FROM [categories]
JOIN [cat] ON [cat].[catnumber] = [categories].[parent]
WHERE
[cat].[status] = '1'
)
SELECT @Ret = (SELECT DISTINCT * FROM [categories] FOR XML PATH(''))
RETURN @Ret
END
#1
3
CREATE FUNCTION [dbo].[fn_qncatxml](@qnnumber INT, @qnversion INT)
RETURNS XML
WITH RETURNS NULL ON NULL INPUT
BEGIN
DECLARE @Ret xml;
WITH [categories] AS
(
SELECT [qn].[number], [qn].[version], [cat].[catnumber], [qncat].[itemnumber], [cat].[parent], [cat].[description], 0 AS [distance]
FROM [qn]
LEFT JOIN [qncat] ON [qncat].[qnnumber] = [qn].[number] AND [qncat].[qnversion] = [qn].[version]
LEFT JOIN [cat] ON [cat].[catnumber] = [qncat].[catnumber] AND [cat].[status] = '1'
WHERE [qn].[number] = @qnnumber AND [qn].[version] = @qnversion
UNION ALL
SELECT [categories].[number], [categories].[version], [cat].[catnumber], [categories].[itemnumber], [cat].[parent], [cat].[description], [categories].[distance] + 1 AS [distance]
FROM [categories]
JOIN [cat] ON [cat].[catnumber] = [categories].[parent]
WHERE
[cat].[status] = '1'
)
SELECT @Ret = (SELECT DISTINCT * FROM [categories] FOR XML PATH(''))
RETURN @Ret
END