Stored Procedure Dilemma 2

[MadJock]

I want to write a stored procedure that will do the following-

accept 2 parameters,
return top records from table based on parameters, set field called 'in_use' on record to TRUE and then return the record selected to the calling application.

The problem with this is that the update query will not return a records, but the 'in_use' field must be set to TRUE at the same time as I return the record. This is to stop other people trying to access the same records.

The records all represent work requests, so we do not want more than 1 person working on the same request, hence the need for update and return.

Any advice will be greatly appreciated,

Graeme

website:

http://www.graeme-taylor.co.uk

 

[joebickley]

I think i get the jist of what ur trying to do but could u put ur existing code up so i could suggest something

 

[oldduffer]

MadJock

Need a bit more information to solve that one explicitly but look at the example below, this sets your in_use field first and then returns the same set of rows, the number of rows returned is based on parameter one and restricted by parameter 2, update this as necessary (and dont worry this will return the same rows it has updated as)

hope this helps
OD


Create YourProcedure @parameter1 char(20), @parameter2 char(20) as

set rowcount @parameter1
update tablename
set in_use = 'true'
where unique_identifier in (SELECT TOP 2 * FROM tablename WHERE some_condition = @parameter2

 

[oldduffer]

sorry wrong code in the above message use the following

Create YourProcedure @parameter1 char(20), @parameter2 char(20) as

set rowcount @parameter1
update tablename
set in_use = 'true'
WHERE some_condition = @parameter2

select * from tablename
WHERE some_condition = @parameter2


thats better

 

[MadJock]

Thanks for help folks but perhaps my explanation wasn't too clear (I am new to this!).

I now have the following stored procedure (the parameters aren't too relevant so I've left them out)

Create Procedure [spGetAndLockOne]
AS
DECLARE @UniqueID INT
@UniqueID = SELECT TOP 1 myTable.ID FROM myTable WHERE in_use = 0
UPDATE myTable SET in_use=1 WHERE myTable.ID = @UniqueID
SELECT * FROM myTable WHERE id = @UniqueID

This has the effect of
Get ID of top record
Update record to show it as in use
Return whole record to calling APP

My potential problem is where user 1 calls the SP, then USER 2 calls it fractionall after User 1 before the in_use field is set to TRUE.

The system is a stack of jibs where each of the users have to action the top one.

I hope that this explains better,

Graeme
website:

http://www.graeme-taylor.co.uk

 

[tlbroadbent]

You can update the In_use column and obtain the uniqueid in the update statment. The first select is not needed.

Create Procedure [spGetAndLockOne]
AS

DECLARE @UniqueID INT

-- Set rowcount rather than use TOP 1
Set rowcount 1

--Inhibit row affected messages
set nocount off

--Update table and get ID
UPDATE myTable
SET in_use=1, @UniqueID=ID
WHERE MyTable.In_Use=0

--Select row
SELECT * FROM myTable
WHERE id = @UniqueID

Set nocount off Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[MadJock]

Thanks alot!! website:

http://www.graeme-taylor.co.uk