How can I use Exec within a Select statement

Question

Post reply

How can I use Exec within a Select statement

danial_i

Mr or Mrs. 500

Points: 508
More actions
February 12, 2007 at 11:46 am

#369454

Hello,
I have a Stored Procedure that gets some inputs and returns output (was converted from a function)
Also, I have a SELECT statement that retrieves data from multiple tables and used to call that function.
I just want to execute the Stored Procedure within the SELECT statement and get one result set
Ex:
SELECT a, b, c, @var=exec sp_name par1, par2 ??? or
SELECT col1, col2, col3, exec SP_name par1, par2 output ??
Any help would be appreciated.
Thx.

D. Danial

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 1

For your requirement, it makes more sense to use a User Defined Function.

Prasad Bhogadi
www.inforaise.com

danial_i Mr or Mrs. 500 Points: 508 More actions · Answer 2

Thanks Prasad!

I'm looking for this as part of a migration to Sybase. Sybase doesn't support user defined function.

DD

D. Danial

Aranda Morrison SSC Enthusiast Points: 165 More actions · Answer 3

You cannot use stored procedures from within a select statement (to my knowledge). Not sure of your requirements exactly but you may need populate a table using the stored proc first, then join to that table in the select statement.

Cheers,

Aranda

CodeDigger Mr or Mrs. 500 Points: 506 More actions · Answer 4

Forget about using stored procedures in SELECTs.

I tried to fool SQLServer once by creating a user-defined function, in which I called the stored procedure and put the results in variables, but no cigar!

Finally, I had to use cursors.

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 5

Well using cursors are least recommended unless otherwise there is no other way to fulfil your requirement. UDF would have been handy if Danial had no limitation with respect to using them in Sybase Migration.

It is always recommended to use Set Based solutions than Cursors.

Thanks

Prasad Bhogadi
www.inforaise.com

sylogic Valued Member Points: 66 More actions · Answer 6

Create a temporary table

insert into <tmp table>

exec sp_abc

After the insert create proper indexes ans then use it in your sql satement

P/S: Most compound sql statements can have multiples instruction by using a "begin" at start and a "End" to close the statement

Begin

Create table ....

insert exec ...

create index ...

select...

from....

join....

end