作者：情人節快樂1991的名单 | 来源：互联网 | 2023-09-06 18:20
sqlserver写了一个函数，ALTERFUNCTION[dbo].[F_MEASURE_TOTAL](@fileTypeASINT,@parentProjectListId
sqlserver 写了一个函数，
ALTER FUNCTION [dbo].[F_MEASURE_TOTAL] (
@fileType AS INT,
@parentProjectListId AS nvarchar(50)
) RETURNS TABLE -- nvarchar
AS RETURN WITH measureTotal AS (
select id ,ord_code,Name ,Total,labor_rate, material_rate,main_material_rate,(material_rate+main_material_rate)as materialAndMainmaterialRate ,machine_rate, (overhead_rate+profit_rate)as overheadAndProfitRate,file_type,parent_project_list_id,
'0' AS measureFlag,
- 1 AS measure_item_id,
CAST (
(
ROW_NUMBER () OVER (ORDER BY mi.id)
) AS nvarchar (MAX)
) rowNo
from
mmmi where mi.measure_title_id in (select mt.id from MeasureTitle mt where mt.name like '%总价%')AND mi.file_type = @fileType
AND mi.parent_project_list_id = @parentProjectListId
UNION ALL
select mni.id , code,mni.Name ,addup,mni.labor_rate, mni.material_rate,mni.main_material_rate,(mni.material_rate+mni.main_material_rate)as materialAndMainmaterialRate ,mni.machine_rate, (mni.overhead_rate+mni.profit_rate)as overheadAndProfitRate,
mni.file_type,mni.parent_project_list_id,
'1' AS measureFlag,
measureTotal.measure_item_id AS measure_item_id,
CAST (
(
measureTotal.rowNo + '.' + CAST (
(
ROW_NUMBER () OVER (ORDER BY mni.id)
) AS nvarchar (MAX)
)
) AS nvarchar (MAX)
) rowNo
from ddmni,
measureTotal
WHERE
mni.measure_item_id = measureTotal.id
AND mni.file_type = @fileType
AND mni.parent_project_list_id = @parentProjectListId
) SELECT
convert(float,measureTotal.rowNo)as rowNo2,*
FROM
measureTotal;
之前使用没有问题，但是最近突然就报了上面的错，看网上指点，说加上OPTION (MAXRECURSION 0);就可以了 但是为什么，我加上这句之后就报错，
，跪求大神指点！
14 个解决方案
本帖最后由 roy_88 于 2016-04-28 21:11:03 编辑
把以下红色位置，定义好列名就行了
ALTER FUNCTION [dbo].[F_MEASURE_TOTAL] (
@fileType AS INT,
@parentProjectListId AS nvarchar(50)
)
RETURNS @T TABLE(ID INT,ord_code VARCHAR(1000),Name VARCHAR(1000),.....) -- nvarchar -- nvarchar
AS
BEGIN
WITH measureTotal AS (
select id ,ord_code,Name ,Total,labor_rate, material_rate,main_material_rate,(material_rate+main_material_rate)as materialAndMainmaterialRate ,machine_rate, (overhead_rate+profit_rate)as overheadAndProfitRate,file_type,parent_project_list_id,
'0' AS measureFlag,
- 1 AS measure_item_id,
CAST (
(
ROW_NUMBER () OVER (ORDER BY mi.id)
) AS nvarchar (MAX)
) rowNo
from
mmmi where mi.measure_title_id in (select mt.id from MeasureTitle mt where mt.name like '%总价%')AND mi.file_type = @fileType
AND mi.parent_project_list_id = @parentProjectListId
UNION ALL
select mni.id , code,mni.Name ,addup,mni.labor_rate, mni.material_rate,mni.main_material_rate,(mni.material_rate+mni.main_material_rate)as materialAndMainmaterialRate ,mni.machine_rate, (mni.overhead_rate+mni.profit_rate)as overheadAndProfitRate,
mni.file_type,mni.parent_project_list_id,
'1' AS measureFlag,
measureTotal.measure_item_id AS measure_item_id,
CAST (
(
measureTotal.rowNo + '.' + CAST (
(
ROW_NUMBER () OVER (ORDER BY mni.id)
) AS nvarchar (MAX)
)
) AS nvarchar (MAX)
) rowNo
from ddmni,
measureTotal
WHERE
mni.measure_item_id = measureTotal.id
AND mni.file_type = @fileType
AND mni.parent_project_list_id = @parentProjectListId
)
INSERT INTO @T(ID,ord_code,NAME,.......)
SELECT
convert(float,measureTotal.rowNo)as rowNo2,*
FROM
measureTotal
OPTION (MAXRECURSION 0);
RETURN
END
GO
看一下tempdb是否限制大小？所在硬盘空间是否已满？
报错与数据有关，你要检查你自身数据会不会出现死循环
在相关条件列加上索引
以上只是调整方法，出不了结果参照以上回复#3#4
如果本身你的语句就存在逻辑问题或数据问题没人能帮到你，你只有自己去测，大家只能提供方法
以上方法只是解决你的递归100限制问题，如果是数据或逻辑引起的死循环，除非找到问题据在，列出数据，还可以帮你解决，如果问题你都不知道,没人能帮到你，你只能在语句方法和数据自己检查
那是因为之前递归的没有那么多层,随着业务变化,就达到了这些层了.
京公网安备 11010802041100号