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

Webbo_1980 used Ask the Experts™
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?

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
> 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
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
>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