函数名称在SQL Server中不明确 [英] Function name is ambigious in SQL server
问题描述
基于此问题,我创建了一个函数并将其添加到我的数据库中。现在我想将该函数用作计算列:
Based on this question, I've created a function and added it to my database. Now I want to use that function as a calculated column:
-- Based on https://stackoverflow.com/a/1012802/10406502 (work by Even Mien)
CREATE FUNCTION [dbo].[StripCharacters]
(
@String NVARCHAR(MAX),
@MatchExpression VARCHAR(255)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
SET @MatchExpression = '%['+@MatchExpression+']%'
WHILE PatIndex(@MatchExpression, @String) > 0
SET @String = Stuff(@String, PatIndex(@MatchExpression, @String), 1, '')
RETURN @String
END
GO
-- Table
CREATE TABLE [dbo].[Trailer]
(
[ID] INT NOT NULL PRIMARY KEY IDENTITY,
[ID_Hauler] INT NULL,
[RegistrationNumber] NCHAR(9) NOT NULL,
[RegistrationNumberSimplified] AS [dbo].StripCharacters([RegistrationNumber], '^A-Z0-9'),
[MaxLoad] FLOAT NULL,
CONSTRAINT [FK_Hauler_Trailer] FOREIGN KEY ([ID_Hauler]) REFERENCES [Hauler]([ID]),
CONSTRAINT [UC_RegistrationNumber] UNIQUE ([RegistrationNumberSimplified])
)
但是,我引用该函数的行抛出错误:
However, the line where I reference the function throws an error:
Berechnete Spalte:[dbo]。[Trailer]。[RegistrationNumberSimplified]
请参见Verweis auf ein Objekt。交易者,交易员,交易员,交易员:[dbo]。[StripCharacters]交易者
[dbo]。[Trailer]。 [dbo] :: [StripCharacters]。
"Berechnete Spalte: [dbo].[Trailer].[RegistrationNumberSimplified]" enthält einen nicht aufgelösten Verweis auf ein Objekt. Entweder ist das Objekt nicht vorhanden, oder der Verweis ist mehrdeutig, da er auf die folgenden Objekte verweisen könnte: [dbo].[StripCharacters] oder [dbo].[Trailer].[dbo]::[StripCharacters].
要么该对象不存在,要么该引用不明确,因为它可能意思是[dbo]。[StripCharacter]或[dbo]。[Trailer]。[dbo] :: [StripCharacter]。
"Either the object doesn't exist, or the reference is ambigious, because it could mean either [dbo].[StripCharacter] or [dbo].[Trailer].[dbo]::[StripCharacter]."
我也试图让服务器猜测函数的名称空间。在这种情况下,数据库将引发错误:
I've also tried to let the server guess the namespace of the function. In that case, the database throws an error:
(57,1):SQL72014:.Net SqlClient数据提供程序:Meldung 195,Ebene 15,
状态10,Zeile 11'StripCharacters'不是公认的内置
函数名称。
(57,1): SQL72014: .Net SqlClient Data Provider: Meldung 195, Ebene 15, Status 10, Zeile 11 'StripCharacters' is not a recognized built-in function name.
这里出了什么问题?
我还发现了这个问题,但答案无济于事,因为我不使用数据库引用。
I also found this question, but the answer doesn't help me, because I don't use database references.
推荐答案
真是个奇怪的问题!将函数的源代码复制到我的数据库项目中新创建的标量函数
文件中,然后删除原始文件并随后重命名新创建的文件之后,它终于可以工作了。我猜因为我根本没有更改任何代码,所以这是Visual Studio的一个非常奇怪的错误。
What a strange problem! After copying the source code of the function into a newly created scalar function
file in my database project, and then deleting the original file and renaming the newly created one afterwards, it finally works. Because I didn't change any code at all, I guess, that this was some pretty weird bug of Visual Studio.
这篇关于函数名称在SQL Server中不明确的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!