Executing a procedure for every selected record

In a few places in my application I want to execute a procedure for every selected record. The only way that I know how to do this is to use a cursor, as below. This code works perfectly, but everything I read says that one should use set operations rather than cursors wherever possible, as they are much more efficient. So what I really want to do is something like
Exec procedure argument [,argument]... where argument in (Selet value from ...)
or perhaps
Exec procedure (select ...) [,argument]
or
SELECT (procedure...

all of which are invalid. Is there any syntax that avoids the cursor in: -

--Copy all facts (and their details, if any) to the new INDIDeclare ccopyIndicursor for--Step through factsSelect Factidfrom gdbfactwhere factindiid = @.IndiidFromOpen ccopyIndiFETCH Next from ccopyIndiinto @.Factidwhile@.@.Fetch_status = 0Beginexec dbo.gdbcopyfact @.NewIndiid, @.FactidFetch next from ccopyIndiinto @.FactidEndCLOSE ccopyIndiDEALLOCATE ccopyIndi

(BTW, dbo.gdbcopyfact is NOT a simple INSERT statement)

The is one of the occasions where you really have to use a cursor. The other option is to create a new SP (gdbcopyfactbyfactindiid) which performs a set based copy (can't say if that's possible without seeing the code for gdbcopyfact)

|||

Let say that, the dbo.gdbcopyfact stored procedure having an Insert statment where you insert the values of the two parameters you passed to the SP.

So, just do this:

12-- Example34INSERT INTO MyTable5SELECT value1, value2 FROM SourceTable6GO

Where,

MyTable: is the table that you insert data in inside the stored procedure (SP).

SourceTable: repersents the gdbfact table in your example.

value1: represent the first column name that you get its value in the cursor's select.

value2: represent the second column name that you get its value in the cursor's select.

and so on for other columns values (if needed).

This will do the job in one time with only two line of codes!

|||

But, since Robert said "dbo.gdbcopyfact is NOT a simple INSERT statement", it's not necessarily that simple.

|||

Robert: If you have to do a certin operation on each of the records in the table, then the cursor is the only option you have (as I know).

If not, then the sample I posted eairler will do the job just fine.

Regards,
CS4Ever

|||

Thank you Gunteman, I thought that would be the answer, but at least now I'm certain that I haven't missed anything. As you commented in response to CS4Ever's post, if the procedure HAD been a simple insert then I would have known what to do, but I made a point of saying that it wasn't. Also, I was looking for a general approach that will work in this and other situations.

If anybody from the SQL design team is listening, what I'd REALLY like to see in some future release is
SELECT ...... BEGIN
...
END [SELECT]
with the rather obvious semantics that the statements between SELECT ... BEGIN and END SELECT are executed for each record. This should be a relatively easy extension to T-SQL that would not invalidate any previous programs. This (except that the keyword was DO instead of BEGIN) was syntax included in a 4GL that we developed ~ 20 years ago (PL/I, mainframe), and it worked very well. An alternative that is also compatible with current T-SQL syntax is
WHILE SELECT ....
...
END [WHILE]

Aesthetically I prefer the first option.

Thank you

sql