July 11, 2002 at 2:56 pm
Hi,
I have an IVR(Interactive Voice Response) application already in production. We are now creating a web version of the application. I want to reuse as many of the stored procedures as possible. I would need to call a few stored procedures from the web. To limit the number of calls to the database I was thinking of calling a single stored procedure which would call the other stored procedures.
Problem: The earlier stored procedures don't use Output parameters. They return the results as a resultset. For example:
Create procedure sp1 as
SELECT Max(IVRID) IVRID
FROM IVRLog
GO
The single stored procedure from the web would look like this:
Create stored procedure websp as
exec sp1
exec sp2 @IVRID
GO
The @IVRID would be the value in sp1.
My question is: Is there anyway I can access the IVRID from websp without changing sp1?
I hope you guys can help me with this.
Thanks,
Vidya.
July 11, 2002 at 3:11 pm
Stored procs can return multiple recordsets, using ADO pretty easy to loop through them and use. Only thing is that you have to refer to the recordsets by ordinal position, no way that I know of to name them.
Andy
July 11, 2002 at 3:18 pm
Andy,
I don't want to select the resultset of sp1 in ASP. I want to do it in the stored procedure websp. Is there any way of doing it?
Thanks,
Vidya
July 11, 2002 at 4:25 pm
Normal way is to use insert into ...exec proc, where the target is a temp table. I think you can also open the proc via openrowset and treat it as a table, not sure of the amount of overhead associated with that approach.
Andy
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply