jc64
asked on
Transact SQL Programming
I have two stored procedured the second depends the first.
I call the first with a set of parameters and for each record returned call another stored procedure
with paramter values obtained from the result of running the first.
Asume knowing a phone number, I call the first sp with the phonenumber as a paramter, it returns records
containinig first and last names. Then for each record returned by the first I call the second stored
procedure and I pass the last name the second sp returns the name of a city correcponding with the last
name passed to it.
The result should contain for each record
FirstName LastName City
Thank you
I call the first with a set of parameters and for each record returned call another stored procedure
with paramter values obtained from the result of running the first.
Asume knowing a phone number, I call the first sp with the phonenumber as a paramter, it returns records
containinig first and last names. Then for each record returned by the first I call the second stored
procedure and I pass the last name the second sp returns the name of a city correcponding with the last
name passed to it.
The result should contain for each record
FirstName LastName City
Thank you
ASKER
thanks nigelrivett for you answer but i need you to be a bit more specific as I am a beginner.
Here is my first attempt to populate the temp file using the result from the stored procedure sp1 that takes a phone number as a paramter and return a list of first and last names for the phone.
I am getting here a sql sytax error
insert * into #my_temp
from sp1('333-444-5555')
send if we succeed populating the temp table how can we use the data in there to call the other sp2 to find the city name corresponding each last name in temp table. asume once provided with last name sp2 return one record.
Thanks
Here is my first attempt to populate the temp file using the result from the stored procedure sp1 that takes a phone number as a paramter and return a list of first and last names for the phone.
I am getting here a sql sytax error
insert * into #my_temp
from sp1('333-444-5555')
send if we succeed populating the temp table how can we use the data in there to call the other sp2 to find the city name corresponding each last name in temp table. asume once provided with last name sp2 return one record.
Thanks
The syntax for populating the table is
insert #temp
exec sp1 '....'
You will probably get something like this
create table #a
(firstname varchar(20), lastname varchar(20), city varchar(20) null)
insert #a exec sp1 '.....'
declare @lastname varchar(20) ,
@city varchar(20)
while exists (select * from #a where city is null)
begin
set @lastname = (select top 1 lastname from #a where city is null)
exec sp2 @lastname, @city output
update #a set city = coalesce(@city,'') where lastname = @lastname
end
you should then end up with a populated #a.
insert #temp
exec sp1 '....'
You will probably get something like this
create table #a
(firstname varchar(20), lastname varchar(20), city varchar(20) null)
insert #a exec sp1 '.....'
declare @lastname varchar(20) ,
@city varchar(20)
while exists (select * from #a where city is null)
begin
set @lastname = (select top 1 lastname from #a where city is null)
exec sp2 @lastname, @city output
update #a set city = coalesce(@city,'') where lastname = @lastname
end
you should then end up with a populated #a.
ASKER
To resolve the idea for dumping the result of the stored procedure into temp table here is the sytax i used but I am still getting an error.
Here is the result of the stored procedure.
The command is
exec dbo.ps1
The result is
fname lname
John Doe
(return status = 0)
Now I want to insert the result of the stored procedure into temp file #a here is my apprauch
create table #a(fname char(50), lname char(50))
go
insert #a exec dbo.sp1
here is the error
Server message 156, Severity 15, State 1, Line 2
Here is the result of the stored procedure.
The command is
exec dbo.ps1
The result is
fname lname
John Doe
(return status = 0)
Now I want to insert the result of the stored procedure into temp file #a here is my apprauch
create table #a(fname char(50), lname char(50))
go
insert #a exec dbo.sp1
here is the error
Server message 156, Severity 15, State 1, Line 2
Get rid of the go (should affect anything but won't work in an sp).
message 156 is
Incorrect syntax near the keyword '%.*ls'.
You should have got this message telling you what the keyword is.
try this
create procedure spNR
as
select 'qwer','asdf'
go
create table #a (s varchar(10), t varchar(10))
insert #a exec spNR
select 'hi there', * from #a
drop table #a
message 156 is
Incorrect syntax near the keyword '%.*ls'.
You should have got this message telling you what the keyword is.
try this
create procedure spNR
as
select 'qwer','asdf'
go
create table #a (s varchar(10), t varchar(10))
insert #a exec spNR
select 'hi there', * from #a
drop table #a
ASKER
I run the things exactly as you said but I still get error
from insert #a exec spNR by the way I am sorry that I didn't point out to you that I am using Sybase 11. Will that make a difference.
Thanks
from insert #a exec spNR by the way I am sorry that I didn't point out to you that I am using Sybase 11. Will that make a difference.
Thanks
ops! ;-)))
</wqw>
</wqw>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Consider populating a temp table with first name and last names in the first SP and using that in the second rather than calling row by row.
The second SP can then update the temp table with the city (what if names aren't unique).
If you want to call with parameters just put an identity on the temp table and loop through using a variable to access row by row calling the SP with parameters and getting the city in an output parameter to update the row.