[sql] Calling Scalar-valued Functions in SQL

I have migrated a database from oracle, and now have a few Scalar-valued Functions.

However, when I call them, I get an error saying:

Cannot find either column "dbo" or the user-defined function or aggregate "dbo.chk_mgr", or the name is ambiguous.

I'm calling it like this:

SELECT dbo.chk_mgr('asdf')

What am I doing wrong?

This question is related to sql tsql sql-function

The answer is

Are you sure it's not a Table-Valued Function?

The reason I ask:

CREATE FUNCTION dbo.chk_mgr(@mgr VARCHAR(50)) 
RETURNS @mgr_table TABLE (mgr_name VARCHAR(50))
  INSERT @mgr_table (mgr_name) VALUES ('pointy haired boss') 

SELECT dbo.chk_mgr('asdf')


Msg 4121, Level 16, State 1, Line 1
Cannot find either column "dbo" or the user-defined function 
or aggregate "dbo.chk_mgr", or the name is ambiguous.


SELECT * FROM dbo.chk_mgr('asdf') 

pointy haired boss

Make sure you have the correct database selected. You may have the master database selected if you are trying to run it in a new query window.

That syntax works fine for me:

CREATE FUNCTION dbo.test_func
(@in varchar(20))
    RETURN 1

SELECT dbo.test_func('blah')

Are you sure that the function exists as a function and under the dbo schema?

You are using an inline table value function. Therefore you must use Select * From function. If you want to use select function() you must use a scalar function.


Can do the following

PRINT dbo.[FunctionName] ( [Parameter/Argument] )


PRINT dbo.StringSplit('77,54')

Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to tsql

Passing multiple values for same variable in stored procedure Count the Number of Tables in a SQL Server Database Change Date Format(DD/MM/YYYY) in SQL SELECT Statement Stored procedure with default parameters Format number as percent in MS SQL Server EXEC sp_executesql with multiple parameters SQL Server after update trigger How to compare datetime with only date in SQL Server Text was truncated or one or more characters had no match in the target code page including the primary key in an unpivot Printing integer variable and string on same line in SQL

Examples related to sql-function

Select Rows with id having even number How to filter Pandas dataframe using 'in' and 'not in' like in SQL Calling Scalar-valued Functions in SQL How to check date of last change in stored procedure or function in SQL server How to check if a function exists on a SQL database SQL Query - Concatenating Results into One String MySQL stored procedure vs function, which would I use when? MySQL: Selecting multiple fields into multiple variables in a stored procedure How to Replace Multiple Characters in SQL? Function vs. Stored Procedure in SQL Server