SQLServer cannot find my user defined function fun

Question

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/

Answer 1

Works for me.

Try CREATE FUNCTION 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.

Answer 2

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.

Answer 3

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.

Answer 4

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 use SELECT * FROM functionName()

Answer 5

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

1. you are using the wrong function name you need to add dbo.function name to get it.
2. 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.

Answer 6

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:

CREATE FUNCTION dbo.ufTest