Menu
Suppose that I have a SQL function called MAGIC that returns the magical value of a number.
Suppose further that I want to write a SQL query that will return a list of numbers from a SQL table along with their magical values.
My instinct is to write
SELECT number, MAGIC(number) FROM NUMBERS;
However, this returns the error message `MAGIC is not a recognized function name. How can I make this work?
EDIT: It looks like MAGIC is set up to be a table-valued function even though it returns only a single value. My DBA will not give me access to any database function code, so I have to work with it that way.
848
A function can be called by specifying its name and parameter list wherever an expression of the appropriate data type may be used. To show how stored functions can be called, we'll use the simple stored function shown in Example 10-6.
We can use a function anywhere such as AVG, COUNT, SUM, MIN, DATE, and other functions with the SELECT query in SQL. Whenever a function is called, it compiles. Functions must return a value or result. Functions use only input parameters.
If it is a scalar-value function, fully qualify your function in the TSQL:
SELECT number, dbo.MAGIC(number) FROM NUMBERS;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
