CODEWITHSUNDEEP
sqlsql-server
Vivian River
Vivian River

Reputation: 32390

How do I use a SQL function inside my SQL query

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?

MAGIC is a table-valued function even though it returns only a single value.

Upvotes: 5

Views: 64878

Answers (3)

prilldev
prilldev

Reputation: 304

If it's a table-valued function, then just do this:

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

Upvotes: 2

hector teran
hector teran

Reputation: 379

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

Upvotes: 0

C-Pound Guru
C-Pound Guru

Reputation: 16358

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

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

Upvotes: 5

Related Questions