How do I use a SQL function inside my SQL query

Suppose that I have a SQL function called MAGIC that retu开发者_Python百科rns 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.

If it is a scalar-value function, fully qualify your function in the TSQL:

SELECT number, dbo.MAGIC(number) FROM NUMBERS;

If its a table-valued function, then just do this:

SELECT n.number, mn.number FROM numbers as n CROSS JOIN dbo.Magic(n.number) as mn

Try using the function like a field... this is for TSQL.

SELECT number,
-- start function
(SELECT function_field_name FROM dbo.MAGIC(number)) AS magic_number
-- end function 
FROM dbo.NUMBERS