Question executing a Store Procedure

Question

Question executing a Store Procedure

EIAnd

Right there with Babe

Points: 743
More actions
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

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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

EIAnd Right there with Babe Points: 743 More actions · Answer 2

EIAnd

Right there with Babe

Points: 743

February 21, 2009 at 2:21 pm

#948217

ty very much Gail Shaw it help me a lot.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

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)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

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