SQL - Stored Procedure or Function inside a SELECT

Hi,

Is it possable to run a SQL Stored Procedure or a Function inside a SELECT statement?

If so, can you please give me an example? :)

Thank you!
EndelmAsked:
Who is Participating?
 
Aneesh RetnakaranDatabase AdministratorCommented:
u can call functions inside a select statement

create function dbo.RetSum(@i int, @j int)
returns int
 as
begin
return (@i+@j)
end
go

SELECT dbo.RetSum(1,2)
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Is it possable to run a SQL Stored Procedure inside a SELECT statement?
no

>Is it possable to run a SQL Stored Function inside a SELECT statement?
yes, see comment of aneeshattingal
0
 
Scott PletcherSenior DBACommented:
You can use a scalar function in place of any single value in a SELECT, so it can appear in the SELECT list, WHERE clause, etc..

You can use table-based functions in place of a table within a SELECT ** but the parameters to the table-based functions must be fully known at the start of the query, that is, you cannot use a result column of a query in a table-based function w/i a query**.  For example, this is OK:
DECLARE @date DATETIME
SET @date = GETDATE() - 30
SELECT ...
FROM dbo.aTableBasedFunction (@date)
since @date is known when the SELECT is started.  This is NOT allowed:
SELECT ...
FROM table1
CROSS JOIN dbo.aTableBasedFunction (table1.column1)
since "table1.column1" varies during the query, it cannot be resolved *prior* to running the SELECT, as required.
0
 
Scott PletcherSenior DBACommented:
For scalar functions, otoh, you can use table columns.  For example, this:

SELECT dbo.aScalarFunction(table1.column1) AS [Single-Value Function Result]
FROM table1
WHERE dbo.anotherScalarFunction(table1.column2) = 'ABC'

is OK.
0
 
EndelmAuthor Commented:
Thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.