We help IT Professionals succeed at work.

Transact SQL Programming

jc64
jc64 asked
on
Medium Priority
261 Views
Last Modified: 2012-08-13
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
Comment
Watch Question

There doesn't seem to be a problem with this.
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.

Author

Commented:
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
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.

Author

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

Author

Commented:
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
wqw

Commented:
ops! ;-)))

</wqw>
ditto.

Guess it does - try posting in the sybase forum (MS-SQL stands for sql server).
More likely to get a sybase person there.
This has slipped down the list a bit so doubtful if anyone will notice it.

Can post a question i community support to delete this question and refund the points if you want.

Good luck.
(Bit surprised this doesn't work - thought it was available when sql server and sybase were the same product but maybe not - I'm obviously too young to have been around then).

Explore More ContentExplore courses, solutions, and other research materials related to this topic.