How To use Select with Stored procedure - Please Help

[azziuhc]

I am using sql 2000:
I need to select some fields from an already defined complex stored procedure.
and then calculate some var based on returns fields from the stored procedure.

i have :
p1=pass parameter1...
stproc(p1,p2,p3) which returns table with field1,field2,field3
I need to create a select which return field1,field3, newfield4
newfield4=based on p2 and p3

How can I do this using a select command
Please advice/Help
Thank you
dre

 

[markros]

create table #TempProcResults (Field1 ..., Field2, ..)
insert into #TempProcResults execute stProc(p1, p2,p3)

select *, Field2/Field3 as NewField from #TempProcResults

drop table #TempProcResults

PluralSight Learning Library

 

[azziuhc]

Thank you for your quick response.
Your response will give me a startup where to begin
But I am getting one error when executing the stored procedure.

When executing from sql:
exec stProc
@customerid = 1010,
@FromDate = '04-01-2010',
@ToDate = '04-30-2010',
@location = 100002

it works ok
but when i tried from sql
exec stProc(1010,'04-01-2010','04-30-2010',100002)
it is giving me an error with Incorrect syntax near 1010

and it is the same error whne i tried via creating the temp table:
------
create table #TempTable (Item char(30), desc char(30),fDate date,fDate date,Qty int,Location int)
insert into #TempTable execute stProc(1010,'04-01-2010','04-30-2010',100002)
select * from #TempTable
drop table #TempTable
------
Please advice
Thank you
Dre

 

[azziuhc]

I found the error
It should have stated without the parentheses since it is a stored procedure and not a function

exec stProc 1010,'04-01-2010','04-30-2010',100002
works ok
therefore
create table #TempTable (Item char(30), desc char(30),fDate date,fDate date,Qty int,Location int)
insert into #TempTable execute stProc 1010,'04-01-2010','04-30-2010',100002
select * from #TempTable
drop table #TempTable

works ok
Thank you for your help
Dre

 

[markros]

Yes, I was not 100% sure about () when I wrote it.

PluralSight Learning Library

 

[markros]

I also learn one more way of doing it by using cursor as output parameter (never used it before). Take a look

http://www.informit.com/articles/article.aspx?p=25288&seqNum=9

PluralSight Learning Library