Post reply

Question executing a Store Procedure

  • February 21, 2009 at 1:22 pm

    #128531

    I have 2 store procedures that throws several data, and i need to create another store procedure and sum the data that trows the 2 store procedure.

    how can i do this?

    i have tried:

    create procedure Nuevo @Idioma varchar(20),@FechaInicio varchar(10), @FechaFin varchar(10), @Tipo varchar(10)

    as

    select A.Result1+B.Result1

    from (exec spTotalesAdquisicionesComprasIdiomas @Idioma ,@FechaInicio, @FechaFin, @Tipo) A,

    (exec spTotalesAdquisicionesCanjeDonacionIdiomas @Idioma ,@FechaInicio, @FechaFin, @Tipo) B

    But it doesnt work, if somebody knows how to resolve this whit this metod or any other thank you very much.

    bye

  • February 21, 2009 at 1:50 pm

    #948214

    You can't select from a stored procedure. What you'll have to do if you want to go this kind of route is to define temp tables that match the results of those procedures, insert the results of the proc into the temp tables and then use the temp tables in your select.

    For example, if I have a proc that returns a two column result set, int and varchar(20), I can do this.

    CREATE TABLE #ProcResults (SomeColumn INT, SomeOtherColumn VARCHAR(20))

    INSERT INTO #ProcResults (SomeColumn, SomeOtherColumn)

    EXEC SomeProcedure

    SELECT * FROM #ProcResults

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 21, 2009 at 2:21 pm

    #948217

    ty very much Gail Shaw it help me a lot.

  • February 21, 2009 at 4:19 pm

    #948233

    GilaMonster (2/21/2009)

    You can't select from a stored procedure.

    Actually, you can...

    SELECT *

    FROM OPENROWSET('SQLOLEDB','Server=yourserverinstancehere;Trusted_Connection=Yes;Database=Master',

    'Set FmtOnly OFF; EXEC dbo.sp_Who') d

    WHERE d.Spid < 50

    Just keep in mind that the stored procedure can't have a temp table in it or it won't return anything.

    --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

  • February 22, 2009 at 1:31 am

    #948250

    Ok, that works, but...

    Do you really want a second open connection, all the fun of distributed transactions and the like just to avoid a temp table? Plus you'd have to enable OPENROWSET, as it's disabled by default on 2005

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 11, 2009 at 7:45 pm

    #958031

    Sorry for the late reply. My answer would be "Nope... I'll use a temp table"... just wanted folks to know that it could actually be done.

    --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

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

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