問題描述

我想知道為什么最近我創(chuàng)建了函數(shù)，但 SQL Server 在調(diào)用它們時返回錯誤，以防它們被添加到數(shù)據(jù)庫的函數(shù)列表中.

I wonder why recently I create functions but SQL Server returns error in calling them, in a case they get added to the functions list of the database.

例如對于以下函數(shù)，我得到這樣的錯誤:

For example for the following function I get such a error:

declare @cats nvarchar(1000)
set @cats = 'a|b|c|d|e'

SELECT dbo.fncSplit(@Cats, '|')

錯誤:

找不到列dbo"或用戶定義的函數(shù)或聚合dbo.fncSplit"，或者名稱不明確.

Cannot find either column "dbo" or the user-defined function or aggregate "dbo.fncSplit", or the name is ambiguous.

示例函數(shù):

CREATE FUNCTION [dbo].fncSplit
(    
    @RowData NVARCHAR(MAX),
    @Delimeter NVARCHAR(MAX)
)
RETURNS @RtnValue TABLE 
(
    ID INT IDENTITY(1,1),
    Data NVARCHAR(MAX)
) 
AS
BEGIN 
    DECLARE @Iterator INT
    SET @Iterator = 1

    DECLARE @FoundIndex INT
    SET @FoundIndex = CHARINDEX(@Delimeter,@RowData)

    WHILE (@FoundIndex>0)
    BEGIN
        INSERT INTO @RtnValue (data)
        SELECT 
            Data = LTRIM(RTRIM(SUBSTRING(@RowData, 1, @FoundIndex - 1)))

        SET @RowData = SUBSTRING(@RowData,
                @FoundIndex + DATALENGTH(@Delimeter) / 2,
                LEN(@RowData))

        SET @Iterator = @Iterator + 1
        SET @FoundIndex = CHARINDEX(@Delimeter, @RowData)
    END

    INSERT INTO @RtnValue (Data)
    SELECT Data = LTRIM(RTRIM(@RowData))

    RETURN
END

任何建議或解決方案將不勝感激.

Any suggestion or solution would be highly appreciated.

推薦答案

這是表值函數(shù).語法是

declare @cats nvarchar(1000)
set @cats = 'a|b|c|d|e'

SELECT * from dbo.fncSplit(@Cats, '|')

這篇關(guān)于為什么 SQL Server 找不到新創(chuàng)建的函數(shù)?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！