SQL 用户定义函数，找不到 dbo 或用户定义函数

Question

我使用这个查询在我的数据库中创建了一个函数:

CREATE FUNCTION dbo.fnSplit(
    @sInputList VARCHAR(MAX) -- List of delimited items
  , @sDelimiter VARCHAR(MAX) = ',' -- delimiter that separates items
) RETURNS @List TABLE (item VARCHAR(MAX))

BEGIN
DECLARE @sItem VARCHAR(MAX)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
 BEGIN
 SELECT
  @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
  @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))

 IF LEN(@sItem) > 0
  INSERT INTO @List SELECT @sItem
 END

IF LEN(@sInputList) > 0
 INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END
GO

SQL Server 返回: Command(s) completed successfully.
然后我尝试运行这个查询:

SELECT * FROM maj_Posts a
WHERE FeedID = (SELECT dbo.fnSplit(b.FeedIDs) FROM maj_Magazines b WHERE OwnerID = 1)
ORDER BY countOfComments DESC 

它返回一个错误: Msg 4121, Level 16, State 1, Line 1
Cannot find either column "dbo" or the user-defined function or aggregate "dbo.fnSplit", or the name is ambiguous.
请注意 b.FeedIDs是一个包含逗号分隔数字的字符串，如下所示:1,2,4

我想从 maj_Posts 获取行他们的a.FeedID是 b.FeedIDs 中的数字之一... (例如，如果 b.FeedIDs 是 1,2,4 ，我需要来自 maj_Posts 的行，它们的 FeedID 是 1 或 2 或 4。)

问题是什么？

Answer 1

也许你的意思是:

SELECT * FROM maj_Posts a
LEFT OUTER JOIN dbo.maj_Magazines b
ON 1 = 1 AND b.OwnerID = 1
OUTER APPLY dbo.fnSplit(b.FeedIDs, ',') AS s
WHERE a.FeedID = s.item
ORDER BY countOfComments DESC;

或者

SELECT * FROM maj_Posts a
WHERE FeedID IN 
(
  SELECT item FROM maj_Magazines AS b
  CROSS APPLY dbo.fnSplit(b.FeedIDs, ',') 
  WHERE b.OwnerID = 1
)
ORDER BY countOfComments DESC;