标量值函数返回总为空？

qq_1251942602 2018-11-27 10:53:17

请教一下各位大神，下面这个函数为什么返回值总是为空，实际是查的到的

USE [PLM_228]
GO
/****** Object: UserDefinedFunction [dbo].[f_GetRoughRouteOrderItem] Script Date: 11/23/2018 17:55:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[f_GetRoughRouteOrderItem]
(
@ForgingRMOI uniqueidentifier, --锻造路线点物料订货OBJID
@RoughTaskid nvarchar, --任务类型
@qualification_id uniqueidentifier
)
RETURNS uniqueidentifier
AS
BEGIN
declare @RoughRMOI uniqueidentifier = null;

WITH GetChildOrderItem1 AS
(
SELECT roleA_id,roleB_id FROM T_OrderItemOrder
WHERE roleA_id=@ForgingRMOI AND QUALIFICATION_ID = @qualification_id
UNION ALL
SELECT a.roleA_id,a.roleB_id FROM T_OrderItemOrder a
INNER JOIN GetChildOrderItem1 b ON a.roleA_id = b.roleB_id
WHERE a.QUALIFICATION_ID = @qualification_id
),
GetChildOrderItem2 as(
SELECT roleA_id,roleB_id FROM T_OrderItemOrder
WHERE roleB_id=@ForgingRMOI AND QUALIFICATION_ID = @qualification_id
UNION ALL
SELECT a.roleA_id,a.roleB_id FROM T_OrderItemOrder a
INNER JOIN GetChildOrderItem2 b ON a.roleB_id = b.roleA_id
WHERE a.QUALIFICATION_ID = @qualification_id
)
select top 1 @RoughRMOI = a.roleb_id from
(select a2.roleb_id as roleb_id from GetChildOrderItem2 as a2
join t_taskorderitem b on a2.roleb_id=b.roleb_id AND b.QUALIFICATION_ID=@qualification_id
join t_task c on b.rolea_id=c.obj_id and c.type=@RoughTaskid
union all
select a1.roleb_id as roleb_id from GetChildOrderItem1 as a1
join t_taskorderitem b on a1.roleb_id=b.roleb_id AND b.QUALIFICATION_ID=@qualification_id
join t_task c on b.rolea_id=c.obj_id and c.type=@RoughTaskid) a

return @RoughRMOI;

END

...全文

125 10 打赏收藏转发到动态举报

写回复

用AI写文章

10 条回复

切换为时间正序

请发表友善的回复…

发表回复

吉普赛的歌版主 2018-11-27

打赏
举报

SELECT TOP 1 @RoughRMOI = a.roleb_id
    FROM   (
               SELECT a2.roleb_id         AS roleb_id
               FROM   GetChildOrderItem2  AS a2
                      JOIN t_taskorderitem b
                           ON  a2.roleb_id = b.roleb_id
                           AND b.QUALIFICATION_ID = @qualification_id
                      JOIN t_task c
                           ON  b.rolea_id = c.obj_id
                           AND c.type = @RoughTaskid
               union ALL
               SELECT a1.roleb_id         AS roleb_id
               FROM   GetChildOrderItem1  AS a1
                      JOIN t_taskorderitem b
                           ON  a1.roleb_id = b.roleb_id
                           AND b.QUALIFICATION_ID = @qualification_id
                      JOIN t_task c
                           ON  b.rolea_id = c.obj_id
                           AND c.type = @RoughTaskid
           ) a
WHERE a.roleb_id IS NOT NULL  --加上条件

函数这里改下，再试呢？

二月十六版主 2018-11-27

打赏
举报

引用 6 楼 qq_1251942602 的回复：

基本可以确定select top 1 @RoughRMOI = a.roleb_id from 这里查询完赋值没有赋上，真奇葩

一步一步的就确认了问题。查一下from后边的临时表是否有数据。

qq_1251942602 2018-11-27

打赏
举报

基本可以确定select top 1 @RoughRMOI = a.roleb_id from 这里查询完赋值没有赋上，真奇葩

qq_1251942602 2018-11-27

打赏
举报

引用 4 楼二月十六的回复:

另外检查一下接受是否有问题，比如直接返回1了，接受是否正常

恩恩谢了

二月十六版主 2018-11-27

打赏
举报

另外检查一下接受是否有问题，比如直接返回1了，接受是否正常

qq_1251942602 2018-11-27

打赏
举报

引用 1 楼吉普赛的歌的回复:

不用函数，换成下面这样，直接输出有结果？

DECLARE

	@ForgingRMOI          UNIQUEIDENTIFIER,	--锻造路线点物料订货OBJID

	@RoughTaskid          NVARCHAR,	--任务类型

	@qualification_id     UNIQUEIDENTIFIER



--下面3个变量赋实际值

SET @ForgingRMOI='?'

SET @RoughTaskid='?'

SET @qualification_id='?'



----BEGIN

	DECLARE @RoughRMOI UNIQUEIDENTIFIER = NULL;

	

	WITH GetChildOrderItem1 AS

	(

	    SELECT roleA_id,

	           roleB_id

	    FROM   T_OrderItemOrder

	    WHERE  roleA_id = @ForgingRMOI

	           AND QUALIFICATION_ID = @qualification_id

	    UNION ALL

	    SELECT a.roleA_id,

	           a.roleB_id

	    FROM   T_OrderItemOrder a

	           INNER JOIN GetChildOrderItem1 b

	                ON  a.roleA_id = b.roleB_id

	    WHERE  a.QUALIFICATION_ID = @qualification_id

	),

	GetChildOrderItem2 AS(

	    SELECT roleA_id,

	           roleB_id

	    FROM   T_OrderItemOrder

	    WHERE  roleB_id = @ForgingRMOI

	           AND QUALIFICATION_ID = @qualification_id

	    UNION ALL

	    SELECT a.roleA_id,

	           a.roleB_id

	    FROM   T_OrderItemOrder a

	           INNER JOIN GetChildOrderItem2 b

	                ON  a.roleB_id = b.roleA_id

	    WHERE  a.QUALIFICATION_ID = @qualification_id

	)

	SELECT TOP 1 @RoughRMOI = a.roleb_id

	FROM   (

	           SELECT a2.roleb_id         AS roleb_id

	           FROM   GetChildOrderItem2  AS a2

	                  JOIN t_taskorderitem b

	                       ON  a2.roleb_id = b.roleb_id

	                       AND b.QUALIFICATION_ID = @qualification_id

	                  JOIN t_task c

	                       ON  b.rolea_id = c.obj_id

	                       AND c.type = @RoughTaskid

	           union ALL

	           SELECT a1.roleb_id         AS roleb_id

	           FROM   GetChildOrderItem1  AS a1

	                  JOIN t_taskorderitem b

	                       ON  a1.roleb_id = b.roleb_id

	                       AND b.QUALIFICATION_ID = @qualification_id

	                  JOIN t_task c

	                       ON  b.rolea_id = c.obj_id

	                       AND c.type = @RoughTaskid

	       ) a

	

	SELECT @RoughRMOI;

----END

不用函数写有输出

二月十六版主 2018-11-27

打赏
举报

一步一步调试排查吧，把所有语句删除直接return 1试试，如果能接受到，再加一个简单的查询语句，让返回值肯定有值，然后再慢慢一个一个加where条件。

吉普赛的歌版主 2018-11-27

打赏
举报

不用函数，换成下面这样，直接输出有结果？

DECLARE
	@ForgingRMOI          UNIQUEIDENTIFIER,	--锻造路线点物料订货OBJID
	@RoughTaskid          NVARCHAR,	--任务类型
	@qualification_id     UNIQUEIDENTIFIER

--下面3个变量赋实际值
SET @ForgingRMOI='?'
SET @RoughTaskid='?'
SET @qualification_id='?'

----BEGIN
	DECLARE @RoughRMOI UNIQUEIDENTIFIER = NULL;
	
	WITH GetChildOrderItem1 AS
	(
	    SELECT roleA_id,
	           roleB_id
	    FROM   T_OrderItemOrder
	    WHERE  roleA_id = @ForgingRMOI
	           AND QUALIFICATION_ID = @qualification_id
	    UNION ALL
	    SELECT a.roleA_id,
	           a.roleB_id
	    FROM   T_OrderItemOrder a
	           INNER JOIN GetChildOrderItem1 b
	                ON  a.roleA_id = b.roleB_id
	    WHERE  a.QUALIFICATION_ID = @qualification_id
	),
	GetChildOrderItem2 AS(
	    SELECT roleA_id,
	           roleB_id
	    FROM   T_OrderItemOrder
	    WHERE  roleB_id = @ForgingRMOI
	           AND QUALIFICATION_ID = @qualification_id
	    UNION ALL
	    SELECT a.roleA_id,
	           a.roleB_id
	    FROM   T_OrderItemOrder a
	           INNER JOIN GetChildOrderItem2 b
	                ON  a.roleB_id = b.roleA_id
	    WHERE  a.QUALIFICATION_ID = @qualification_id
	)
	SELECT TOP 1 @RoughRMOI = a.roleb_id
	FROM   (
	           SELECT a2.roleb_id         AS roleb_id
	           FROM   GetChildOrderItem2  AS a2
	                  JOIN t_taskorderitem b
	                       ON  a2.roleb_id = b.roleb_id
	                       AND b.QUALIFICATION_ID = @qualification_id
	                  JOIN t_task c
	                       ON  b.rolea_id = c.obj_id
	                       AND c.type = @RoughTaskid
	           union ALL
	           SELECT a1.roleb_id         AS roleb_id
	           FROM   GetChildOrderItem1  AS a1
	                  JOIN t_taskorderitem b
	                       ON  a1.roleb_id = b.roleb_id
	                       AND b.QUALIFICATION_ID = @qualification_id
	                  JOIN t_task c
	                       ON  b.rolea_id = c.obj_id
	                       AND c.type = @RoughTaskid
	       ) a
	
	SELECT @RoughRMOI;
----END

吉普赛的歌版主 2018-11-27