Post reply

How can I use Exec within a Select statement

  • 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

  • February 12, 2007 at 11:57 am

    #689276

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

     

    Prasad Bhogadi
    www.inforaise.com

  • February 12, 2007 at 12:36 pm

    #689281

    Thanks Prasad!

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

    DD


    D. Danial

  • February 12, 2007 at 9:23 pm

    #689363

    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

  • February 13, 2007 at 2:21 am

    #689387

    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.

  • February 13, 2007 at 3:18 am

    #689395

    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

  • February 13, 2007 at 9:00 am

    #689489

    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

     

     

     

     

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

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