For each record execute stored procedure

Question

Post reply

For each record execute stored procedure

Andrew Deren

Mr or Mrs. 500

Points: 597
More actions
May 18, 2006 at 4:41 pm

#366208

Quite often I need to execute some stored procedure for each record based on some query.
For example:
select id, field1, field2, field3...field-n from table where something
and pass all selected fields into some procedure.
I've been using cursors for this, but when there is several columns involved it's just seems too much to do.
Is there something so that I can do select into stored procedure?
For example:
select id, date from table where something
into nameOfProc

Thanks.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Amit Lohia SSCertifiable Points: 6104 More actions · Answer 1

I am not clear about your requirement. Can you post an example

Kindest Regards,

Amit Lohia

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 2

This is one case where cursors are pretty much required.

There's no automatic syntax for executing a stored proc and taking params from a query result.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Vladan SSC-Insane Points: 22072 More actions · Answer 3

I'm not sure what you need to do, could you describe it a bit more in detail? Maybe temporary tables could help... it depends on what the procedure does and what do you want to pass into the procedure.

lakusha SSCommitted Points: 1857 More actions · Answer 4

With SQL2K there is the unsupported xp_execresultset that was handy. With SQL2k5, you may have to rewrite it in C# I think.

Example (doing an insert but you get the point):

USE TEMPDB

create table test([Id] int identity(1,1) not null, [Name] nvarchar(20) not null)

EXEC master..xp_execresultset N'select ''insert into test([Name]) values('''''' + LastName + '', '' + FirstName + '''''')'' from Northwind.dbo.Employees', N'tempdb'

select * from test

drop table test

That procedure is used for replication, it is fast and stable, but you need to configure the server for replication to use it (just enable it for replication and reboot).

You can of course use dynamic SQL to build a batch.