Post reply

Calling an SP from a Select Statement

  • November 29, 2004 at 3:45 am

    #165586

    Dear All,

    I have a SP which returns let's say 3 values:

    Table "A" has the following fields:

    Field1, Field2, Field3, Field4, Field5, Field6, Field7, Field8, Field9, Field10 

    "Exec dbp.My_SP A.Field1, A.Field2" will return @Value1, @Value2 and @Value3.

    How do I get these 3 values along with other fields of the same table in a single query?

    Thanks,

    Andreas K.

  • November 29, 2004 at 5:51 am

    #531694

    I would research using an in-line function.  You cannot call a stored-procedure from a SELECT but can call functions.  The BOL has decent information on UDFs

     



    Good Hunting!

    AJ Ahrens


    webmaster@kritter.net

  • November 29, 2004 at 8:58 pm

    #531804

    Perhaps instead of a stored procedure, it should be a view so that you can select from it as if it were a table.  Also, take a look at OpenRowSet in Books-on-Line to see how to use the results of a stored procedure as if it were a table... the disadvantage is that a user login and password must be provided in "clear code" which is not usually a good idea.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • November 30, 2004 at 6:32 am

    #531845

    I agree with AJ Ahrens above -- a table-valued function is probably your best bet.  But if you must used a stored procedure, you can use Openquery instead of Openrowset to solve some of the security issues. 

    select * from Openquery(<myServer>, 'exec db.owner.storedProc <parm1>, <parm2>, ...')

    Note the following: 

    1) Must set data access to allow a "distributed" query from a server to itself:  sp_serveroption '<myServer>', 'data access', true

    2) You probably need to specify the full path to the stored proc, since the logon may not go to your particular database. 

    3)  Depending on what's happening inside the stored proc, I've found the need to specify "SET FMTONLY OFF" just prior to the proc name,  Openquery(<myServer>, ' SET FMTONLY OFF exec db.owner.storedProc <parm1>, <parm2>, ...').  This seem especially true if you create temporary tables within the proc.  Simple selects don't need this.

    Make sure you have a primary key or other identifier coming out of the stored proc, so you can join back to your original table to combine columns as you would like.

    Hope this helps,

    Scott Thornburg

    Volt Information Sciences

     

Viewing 4 posts - 1 through 3 (of 3 total)

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