基于此问题,我创建了一个函数并将其添加到我的数据库中。现在我想将该函数用作计算列:
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 stackoverflow/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中不明确
发布评论