SQLServer无法在存储过程中找到我的用户定义函数函数

I must have some permissions wrong, but I can't figure out how. The following code is simplified but I can't even get this to work

我一定是有什么权限错了，但是我不知道怎么做。下面的代码被简化了，但是我甚至不能让它工作

CREATE FUNCTION ufTest 
(
    @myParm int
)
RETURNS int
AS
BEGIN
    DECLARE @Result int

    SELECT @Result = @myParm + 1

    RETURN @Result
END
GO

Then I just want to be able to call the function from a stored procedure:

然后我只想能够调用存储过程中的函数:

CREATE PROCEDURE dbo.[uspGetGroupProfileService]
@id        int
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @otherId int;
    SET @otherId = dbo.ufTest(@id);
END

SQLServer keeps telling me that it can't find dbo.ufTest. It shows up under [DB]\Programmability\Functions\Scalar-valued Functions but I can't figure out how to use it.

SQLServer一直告诉我它找不到dbo.ufTest。它显示在[DB]\编程函数\ scalar -value函数下，但我不知道如何使用它。

Anybody have any idea what I'm doing wrong?

有人知道我做错了什么吗?

7 个解决方案

#1

Works for me.

为我工作。

Try CREATE FUNCTION dbo.ufTest ...

dbo尝试创建函数。ufTest……

I assume your default schema can't be dbo and it's ending up in a different schema. Otherwise the only explanation I can think of is you might need to grant permissions on it.

我假设您的默认模式不能是dbo，并且它最终以不同的模式结束。否则，我能想到的唯一解释是，您可能需要为它授予权限。

#2

Script out the UDF and check the schema name. It's probably not dbo. I would change the UDF definition to specifically include dbo. In other words:

编写UDF脚本并检查模式名。它可能不是dbo。我将修改UDF定义，使其包含dbo。换句话说:

CREATE FUNCTION dbo.ufTest

#3

Try calling it with a select instead of a set. And you checked that out belongs to the dbo schema?

尝试使用select而不是set来调用它。

#4

It appears it might be a bug in the query editor. The Function appears in the tree in the right place but even naming the function dbo.xxxxxx the function doesn't appear in the query editor until you close and open a new session, then it appears if you type in dbo.

看起来它可能是查询编辑器中的一个错误。函数出现在树中的正确位置，甚至命名了函数dbo。在您关闭并打开一个新的会话之前，该函数不会出现在查询编辑器中，然后如果您输入dbo，它就会出现。

If you change the name of the function the old non existing fuction is avalable but not the new name. Refresh doesn't fix this only closing the session and starting a new one.

如果您更改函数的名称，旧的不存在的功能是可用的，但不是新名称。Refresh不会修复这个问题，只是关闭会话并启动一个新的会话。

Why I say this might be a bug is that the permissions properties for Table function includeds a blue link to the schema properties but the Scalar functions it doesn't. So it may be a deeper lying bug in the way the schema is set up in the first place for which there may be a work around. Or maybe the schema in the database I am working on has not been set up correctly.

我之所以说这可能是一个错误，是因为表函数的权限属性包含到模式属性的蓝色链接，而标量函数没有。所以它可能是一种更深层次的谎言，在这个模式建立的过程中，可能会有一个工作。或者可能我正在处理的数据库中的模式没有正确设置。

Hopefully someone else can shine some light on this issue.

希望有人能对这个问题有所启发。

#5

Had the exact same problem and mine got fixed by simply restarting SQL Server Management Studio.

有完全相同的问题，而我的问题是通过简单的重新启动SQL Server Management Studio修复的。

Just posting this in case anyone else did everything right and is still not able to call his function.

如果其他人做对了所有的事情并且仍然不能调用他的函数，就发布这个。

#6

As a last resort if any of the above and especially @jrdev22's answer did not help you (and left you stumped why), restart the SQL Server service in Configuration Manager since restarting the SSMS alone sometimes does not reset everything (e.g. similar to when creating a new login instance but not being able to login with it).

作为最后的手段,如果以上,特别是@jrdev22的回答没有帮助你(左你了为什么),重启SQL服务器重新启动以来,配置管理器ssm孤独有时不重置所有(例如,当创建一个新的登录实例相似但无法登录)。

SQL Server Configuration Manager> SQL Server Services > SQL Server > Restart

#7

If you are unable to find the function that you have just created there are two reasons for it.

如果您无法找到刚刚创建的函数，有两个原因。

you are using the wrong function name you need to add dbo.function name to get it.
您使用的是错误的函数名，您需要添加dbo。函数名来获取它。
I've also found one more issue like even though correct name is entered and also it is existing in the object explorer after refreshing you are unable to find it when you are trying to use the function.
我还发现了另一个问题，比如即使输入了正确的名称，而且它也存在于对象资源管理器中。

In this case simply close the sql server and reopen it and you should be able to see the function.

在这种情况下，只需关闭sql服务器并重新打开它，就可以看到这个函数。

#1