May 28, 2008 at 9:51 am
With T-SQL, can one use the results of a stored procedure within an expression inside a SELECT statement? Or would a table-valued function be in order?
Here’s a simplistic example of what I would like to be able to do:
SELECT
ppl.FirstName,
ppl.LastName,
(
SELECT Age FROM
EXEC uspGetAge @BirthDate = ppl.BirthDate
)
FROM People AS ppl
I'd like not to use a cursor, hoping there is valid syntax with which to do this?
May 28, 2008 at 10:21 am
You can use the
INSERT INTO table EXECUTE stored_proc
to capture the results of a stored proc into a table structure.
Look in BOL for more info.
May 28, 2008 at 10:41 am
[p]You'd probably be able to use a scalar function here, to do this.[/p]
[font="Courier New"]SELECT
ppl.FirstName,
ppl.LastName,
dbo.GetAge(ppl.BirthDate)
FROM People AS ppl
[/font]
Best wishes,
Phil Factor
May 28, 2008 at 10:58 am
Stef Evans (5/28/2008)
With T-SQL, can one use the results of a stored procedure within an expression inside a SELECT statement? Or would a table-valued function be in order?Here’s a simplistic example of what I would like to be able to do:
SELECT
ppl.FirstName,
ppl.LastName,
(
SELECT Age FROM
EXEC uspGetAge @BirthDate = ppl.BirthDate
)
FROM People AS ppl
I'd like not to use a cursor, hoping there is valid syntax with which to do this?
The syntax you're looking at seems to point to a scalar return, in which case Phil's on target. If on the other hand it's not a scalar return (meaning you want more than one value, or a data set), look at possibly using the CROSS or OUTER APPLY clauses to pull data. They tend to suffer somewhat performance-wise versus derived tabled syntaxes (if you can get away with a derived table), but they sure do beat cursor solutions.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply