Call Stored Procedure within a SELECT statement

Hello,
Is it possible to call a SP from within the SELECT part of a SQL query?
For example, it is possible to put a sub-query within the SELECT:
SELECT
a,
b,
id,
(SELECT Tbl2.id from Tbl2 where Tbl2.fk = id) as secondaryID
FROM
Tbl1
So therefore, shouldn't I be able to do something like:
SELECT
a,
b,
id,
(EXEC SP2 id) as secondaryID
FROM
Tbl1
Is this possible? Have I got the syntax wrong.
Andrewmilney_boy wrote:
> Hello,
> Is it possible to call a SP from within the SELECT part of a SQL query?
> For example, it is possible to put a sub-query within the SELECT:
> SELECT
> a,
> b,
> id,
> (SELECT Tbl2.id from Tbl2 where Tbl2.fk = id) as secondaryID
> FROM
> Tbl1
> So therefore, shouldn't I be able to do something like:
> SELECT
> a,
> b,
> id,
> (EXEC SP2 id) as secondaryID
> FROM
> Tbl1
>
> Is this possible? Have I got the syntax wrong.
> Andrew
No that's not possible. Either use a user-defined function instead of a
proc or rewrite the expression from your proc as part of this query.
David Portas
SQL Server MVP
--|||> Is it possible to call a SP from within the SELECT part of a SQL query?
Nope.
You can create a table-valued function, or have a look at other ways to
share data between stored procedures:
http://www.sommarskog.se/share_data.html|||milney_boy wrote:
> Hello,
> Is it possible to call a SP from within the SELECT part of a SQL
> query?
> For example, it is possible to put a sub-query within the SELECT:
> SELECT
> a,
> b,
> id,
> (SELECT Tbl2.id from Tbl2 where Tbl2.fk = id) as secondaryID
> FROM
> Tbl1
> So therefore, shouldn't I be able to do something like:
> SELECT
> a,
> b,
> id,
> (EXEC SP2 id) as secondaryID
> FROM
> Tbl1
>
> Is this possible? Have I got the syntax wrong.
No. You can call a UDF like this, but not a SP. See
http://www.sommarskog.se/share_data.html
Bob Barrows
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Look for OPENQUERY in the syntax:
SELECT *
FROM OPENQUERY(OracleSvr, 'SELECT name, id FROM joe.titles')
GO
HTH, Jens Suessmeyer.|||The problem I have is that the stored procedure I have written returns
a row of an unknown number of columns - hence the fact that i wanted to
just 'plonk' the stored procedure into the SELECT clause.
To make matters worse, the stored procedure mentioned uses dynamic sql
- as in EXEC(@.query) - due to the fact that I am returning a variable
number of columns.
Any ideas?|||milney_boy wrote:
> The problem I have is that the stored procedure I have written returns
> a row of an unknown number of columns - hence the fact that i wanted to
> just 'plonk' the stored procedure into the SELECT clause.
> To make matters worse, the stored procedure mentioned uses dynamic sql
> - as in EXEC(@.query) - due to the fact that I am returning a variable
> number of columns.
> Any ideas?
A query always returns a fixed number of columns. You can't have a
variable number of columns without using dynamic SQL so you'll have to
put the entire query in a proc or construct it client-side. Why don't
you know the columns at design time?
David Portas
SQL Server MVP
--|||The reasons is that I am converting from a table of none or more rows
per user to a column layout:
user | letter
1 A
1 F
1 X
2 G
2 D
to:
user | letter1 | letter2 | letter3 etc...
1 A F X
2 G D
Hence, I have written dynamic SQL to return the row structure.
Unfortunately, I also need other information about the user from
another 7 tables, so I need to join the SProc output to other queries.|||then why not join the SProc *input* and then do the conversion (it's
called crosstab and as far as I'm concerned the reference on this is:
description is on
http://weblogs.sqlteam.com/jeffs/ar...05/02/4842.aspx
updated version is on http://weblogs.sqlteam.com/jeffs/articles/5120.aspx
)
milney_boy wrote:
> The reasons is that I am converting from a table of none or more rows
> per user to a column layout:
> user | letter
> 1 A
> 1 F
> 1 X
> 2 G
> 2 D
> to:
> user | letter1 | letter2 | letter3 etc...
> 1 A F X
> 2 G D
> Hence, I have written dynamic SQL to return the row structure.
> Unfortunately, I also need other information about the user from
> another 7 tables, so I need to join the SProc output to other queries.
>