How do i Return a value from a stored procedure in the select statement of another query

Webbo_1980
Webbo_1980 used Ask the Experts™
on
Lets say for example that i have the following command

select column1, column2,column3, (select count(*) from tableb) as column4
from tablea

Now lets say that my real query was far larger, and far more complex and i wanted to place column4 as a stored procedure or function.

Is this possible? and are there any drawbacks?

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
Commented:
> and i wanted to place column4 as a stored procedure or function.
a scalar function, yes, that is possible

>and are there any drawbacks?
possibly, the function will result in better or slower query, this depends ...
HainKurtSr. System Analyst
Commented:
most probably it will be slower if you convert it to a function...

I am not sure if there is a difference between these two after you convert it to a function:

select column1, column2,column3, getCount(column1) as column4 from tablea where ...

select t.*, getCount(column1) as column4 (
  select column1, column2,column3 from tablea where ...
) t

AneeshDatabase Consultant
Top Expert 2009
Commented:
>column4 as a  function
Only if the TableName is static, you cannot pass the tableName to function ( another option will be to query the system table to get the no of rows, this may not be always accurate )  

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial