Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How To use Select with Stored procedure - Please Help

Status
Not open for further replies.

azziuhc

Programmer
Apr 29, 2010
26
US
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
 
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
 
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
 
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

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top