Select and update functions in one stored proc 1

[abs2003]

I don't know if this is possible.

1. select top 100 * from a table.
2. update field on a table as 'in-process' for all 100 records
3. pass those 100 records to my application.

all this has to be done in stored proc.

abs

 

[gmmastros]

It's possible.

Create Procedure GetSomeData
AS
SET NOCOUNT ON

Update MyTable
Set MyField = 'In-Process'
Where Idfield In (Select top 100 IdField From MyTable)

Select Top 100 * From Mytable


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[abs2003]

gmmastros,

Thank you.

 

[gmmastros]

I should mention that you should put an order by in the top 100 queries to ensure you are getting the right data.

Create Procedure GetSomeData
AS
SET NOCOUNT ON

Update MyTable
Set MyField = 'In-Process'
Where Idfield In (Select top 100 IdField From MyTable Order By SomeField)

Select Top 100 * From Mytable Order By SomeField


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom