Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQLServer cannot find my user defined function function in stored procedure

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/

like image 623
Josh Russo Avatar asked Jan 15 '11 00:01

Josh Russo


People also ask

Can stored procedure call user defined function?

You can call a stored procedure inside a user-defined function.

How do you see all the UDF defined in the current database?

Lists all user-defined functions (UDFs) for which you have access privileges. This command can be used to list the UDFs for a specified database or schema (or the current database/schema for the session), or across your entire account.


1 Answers

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.

like image 183
Martin Smith Avatar answered Oct 08 '22 20:10

Martin Smith