Surprised no one gave you an up-to-date answer. Cursors are bad. What you want is to move the logic of the SP into a table-valued-function(TVF) and then use CROSS APPLY
Here is a querry I wrote yesterday (don't dwell on the details, just look at the CROSS APPLY
). The CROSS APPLY
creates a union of tables. Each element of this union is generated from the TVF which is parameterised on the row entries of the select statement.
SELECT supt.hostname,supt.scriptname, COUNT(*)
FROM Event_Pagehit eph
INNER JOIN Symboltable_urlpair supf
ON eph.fromPagePair=supf.id
INNER JOIN Symboltable_urlpair supt
ON supt.id=eph.toPagePair
CROSS APPLY dbo.TDFCompanyFormationsUrlClassification(supf.hostname,supf.scriptname) as x
CROSS APPLY dbo.TDFCompanyFormationsUrlClassification(supt.hostname,supt.scriptname) as y
WHERE x.isCompanyFormations=1
AND y.isCompanyFormations=0
GROUP BY supt.hostname,supt.scriptname
ORDER BY COUNT(*) desc
I can use x
and y
as if they were tables pulled in from the FROM
or JOIN
clauses. If I had to write this querry without a TVF it would span a couple of hundred lines.
Note:
If you can't rewrite the SP: you should be able to insert the result of a stored procedure into the result table from a table valued function. I have never done this, and sometimes the different SQL server construct have caveats -- So unless someone says otherwise I assume this is the case.