问题描述
我想知道为什么最近我创建了函数,但 SQL Server 在调用它们时返回错误,以防它们被添加到数据库的函数列表中.
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.
例如对于以下函数,我得到这样的错误:
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"或用户定义的函数或聚合dbo.fncSplit",或者名称不明确.
Cannot find either column "dbo" or the user-defined function or aggregate "dbo.fncSplit", or the name is ambiguous.
示例函数:
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.
推荐答案
这是表值函数.语法是
declare @cats nvarchar(1000) set @cats = 'a|b|c|d|e' SELECT * from dbo.fncSplit(@Cats, '|')