我肯定有一些权限错误，但我不知道怎么回事。下面的代码是简化的，但我什至无法让它工作
CREATE FUNCTION ufTest
(
@myParm int
)
RETURNS int
AS
BEGIN
DECLARE @Result int
SELECT @Result = @myParm + 1
RETURN @Result
END
GO
然后我只想能够从存储过程调用该函数：
CREATE PROCEDURE dbo.[uspGetGroupProfileService]
@id int
AS
BEGIN
SET NOCOUNT ON;
DECLARE @otherId int;
SET @otherId = dbo.ufTest(@id);
END
SQLServer不断告诉我它找不到dbo.ufTest。它显示在 [DB]\Programmability\Functions\Scalar-valued Functions 下，但我不知道如何使用它。
有人知道我做错了什么吗？
编辑
正如下面所选答案所示，您不能总是信任 SSMS Intellisense。除了尝试执行脚本之外，您可以尝试的一件事是使用 CTRL + SHIFT + R
https://blog.sqlauthority.com/2013/07/04/sql-server-如何刷新-ssms-intellisense-cache-to-update-schema-changes/
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.
Anybody have any idea what I'm doing wrong?
EDIT
As indicated by the selected answer below, you can't always trust the SSMS Intellisense. One thing that you can try, other than just trying to execute the script, is forcing an Intellisense refresh with CTRL + SHIFT + R
https://blog.sqlauthority.com/2013/07/04/sql-server-how-to-refresh-ssms-intellisense-cache-to-update-schema-changes/
发布评论
评论（8）
编写 UDF 脚本并检查架构名称。应该不是dbo吧我将更改 UDF 定义以专门包含 dbo。换句话说：
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:
有完全相同的问题，我的问题通过简单地重新启动 SQL Server Management Studio 就得到了解决。
只是发布此内容以防其他人做对一切但仍然无法调用他的函数。
Had the exact same problem and mine got fixed by simply restarting SQL Server Management Studio.
Just posting this in case anyone else did everything right and is still not able to call his function.
我刚刚遇到一个问题，这是错误，并且本专栏上的所有建议也都失败了。
请务必仔细检查您的函数声明类型和该类型的用法。
我声明了一个返回类型表，并尝试使用
Select functionName()调用它，其中我需要使用SELECT * FROM functionName()I just had an issue where this was the error and all of the advice on this column was failing as well.
Be sure double check your function declaration type and usage of that type.
I declared a return-type table and tried to call it with
Select functionName()where I needed to useSELECT * FROM functionName()作为最后的手段，如果上述任何一个，特别是@jrdev22的答案没有帮助您（并且让您困惑为什么），请在服务 “https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-configuration-manager”rel="nofollow noreferrer">配置管理器，因为单独重新启动 SSMS 有时不会重置所有内容（例如，类似于创建新的登录实例但无法使用它登录）。
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).
尝试使用选择而不是集合来调用它。你检查过它属于 dbo 模式吗？
Try calling it with a select instead of a set. And you checked that out belongs to the dbo schema?
看起来这可能是查询编辑器中的错误。该函数出现在树中的正确位置，但即使将函数命名为 dbo.xxxxxx，该函数也不会出现在查询编辑器中，直到您关闭并打开一个新会话，然后如果您键入 dbo，该函数就会出现。
如果更改函数的名称，旧的不存在的函数可用，但新名称不可用。刷新并不能解决这个问题，只能关闭会话并开始新的会话。
为什么我说这可能是一个错误，因为表函数的权限属性包含指向架构属性的蓝色链接，但标量函数却没有。因此，这可能是模式设置方式中存在的一个更深层次的错误，可能有解决方法。或者我正在处理的数据库中的架构可能尚未正确设置。
希望其他人能够对这个问题有所启发。
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.
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.
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.
如果您无法找到刚刚创建的函数，有两个原因。
在这种情况下，只需关闭 sql server 并重新打开它，您就应该能够看到该功能。
If you are unable to find the function that you have just created there are two reasons for it.
In this case simply close the sql server and reopen it and you should be able to see the function.
对我有用。
尝试
CREATE FUNCTION dbo.ufTest ...我假设您的默认架构不能是
dbo并且它最终会采用不同的架构。否则，我能想到的唯一解释是您可能需要授予它权限。Works for me.
Try
CREATE FUNCTION dbo.ufTest ...I assume your default schema can't be
dboand 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.