CODEWITHSUNDEEP
t-sql
Tony
Tony

Reputation: 12695

T-SQL How to run a procedure/function within a SELECT query?

I can't check it right now (don't have a compiler right now), but will that query executes ?

select myTable.id_customer, [my_procedure_or_function](myTable.id_customer)

from myTable

group by myTable.id_customer

that procedure/function returns a NUMERIC(18,0) or NULL

to sum up, I need to select distinct id_customer from that table and for that id - get a number/null value currently associated with that id_customer

Upvotes: 10

Views: 31187

Answers (2)

Orbling
Orbling

Reputation: 20602

As Martin Smith says, providing it is a scalar user-defined function (UDF) which excepts a single argument of the same type as "myTable.id_customer", not a tabular or stored procedure, and the executing connection has execute rights to the UDF, then it should work fine.

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453018

Syntactically it will work for a scalar UDF but not a stored procedure.

select myTable.id_customer, mySchema.myFunction(myTable.id_customer) As myAlias
from myTable
group by myTable.id_customer

However dependant upon what the scalar UDF is doing there may be more performant approaches. If it is looking up a value in another table for example it is often best to simply inline this logic into the query.

Upvotes: 14

Related Questions