say I have a stored procedure called sp_FindUser which has an input parameter of projectID, and an output parameterr of userID

and then I write another stored proceedures
this time it creates a temp table full of project id's

ie

select ProjectID
into #Temp
from projects

now I want to look at all the records in #temp and find the userID using my first stored procedure

In vb I could open up all the records in #temp and loop round executing a function for each projectid.

eg
rs.open (select * from #Temp)
    do until rs.eof
    debug.print rs.fields(Projectid)
    debug.print sp_FindUser(rs.fields(projectid))


etc etc

have you any idea how i go about this?

I think this is a case where you will need to use a cursor which allows similar access to the rows as your VB ADO example. Look in Books On Line for cursor examples.

--

Joe (Microsoft MVP - XML)

Oh no... post the code for your sp_FindUser proc and let's see what we can do... using a cursor here will absolutely kill any notion of performance.

--Jeff Moden

Cheers jeff

here is the stored proceedure I have written

create PROCEDURE dbo.sp_FindUser

(
    @iProjectid INT,
    @iuserID INT OUTPUT
)


AS

SELECT TOP 1 P.projectallocationlogid, P.projectid, P.newuserid, P.createdate
into #temp
FROM ProjectAllocationLog P
WHERE P.projectid=@iProjectid
ORDER BY createdate DESC


SELECT @iuserID = newuserid
FROM #TEMP

this assigns the UserID that I want into the output parameter of the stored Procedure.

now what I wanted to do was call this stored procedure from another
eg

say I made another stored proceedure, which ran its course and gave me a list of projectID's in a table.

how do I then loop round the table and call the FindUser for every project in the new table?

You don't "loop round the table" and expect any kind of performance... you must think in "sets" of data.

Correct me if I'm wrong, but it appears that all you want to find is a set of the latest users that were added to all projects. In order to do that, your sp_FindUser proc finds the latest entry for a given project... instead, you need to find the latest entry for ALL projects at once...

I don't have your table structure nor your data to test with, but something like the following will work and you don't even need a temp table...



--Jeff Moden

Yes, I see what you mean, about looking at the data in sets, I've always looked row by row, if you know what I mean. Anyway, i shall look into it, and try the code you wrote and adapt it to how I need it.

Thank You very much x

:)

Calling a sp from another sp can cost performance. I suggest you use the inner join to perform the task in sp instead of calling another sp.