Retrieve and update the top row in a stored proc - accessed by multiple process

Question

I'm looking at creating a stored proc which is being accessed by multiple processes and wondering, between the two solutions, which is a better one or is there a third solution?

The goal is find the record that has the maximum value in the field status. The field status contains decimal values but defined as a varchar. Once the appropriate record is found, then update the same record (only one record can be updated by the stored proc).

The stored proc will return the cusip in the form of select statement.

Solution:01

Declare @Cusiptable(Cusip varchar(100));
UPDATE  cusiptbl
SET processingdate = GetDate() ,
Status = 'In Progress',
batchid = @batchid_given,
Output Inserted.Cusip into @Cusiptable
From 
(
 select top(1) Cusip from cusiptbl  where batchid = -1 
 order by cast(status as decimal(18,4)) desc  
) Seconds
where cusiptbl.cusip = Seconds.cusip

select Cusip from @Cusiptable

Solution 02:

select top(1)  @CusipToBeUpdated = Cusip from cusiptbl 
        with (UPDLOCK)
        where batchid = -1 
        order by 
        (case when isnumeric(status) = 1 then 
            cast(status as decimal(18,7)) end) 
            desc

-- Update the status to given batchid and status as 'In Progress'
UPDATE  cusiptbl 
SET batchid = @batchid_given,
    processingdate = GetDate() ,
    Status = 'In Progress'
    where cusiptbl.cusip= @CusipToBeUpdated 
            and batchid = -1

 Select @CusipToBeUpdated Cusip

score 0 · Answer 1 · answered Aug 15 '13 at 09:27

The first query will fail while evaluating order by cast(status as decimal(18,4)) for non-numeric values.

Rewrite it using isnumeric(status) = 1 to prevent the error:

From 
(
 select top(1) Cusip from cusiptbl  
 where batchid = -1  AND isnumeric(status) = 1
 order by cast(status as decimal(18,4)) desc  
) Seconds

Both solution should work assuming the transaction isolation level is read committed or higher.

Casting from varchar to numeric prevents from using an index (if is any), if your table is huge then you might consider adding a virtual column to the table, for example:

create table cusiptbl(
  ......
  status varchar(50),
  status_numeric as case when isnumeric(status) = 1 then 
            cast(status as decimal(18,7)) else null end
)

and create an index on this virtual column:

create index num_status on cusiptbl( status_numeric )

or maybe a composite index (since your queries filter rows using batchid=-1 condition and then order selected rows by status):

create index num_status_batch on cusiptbl( batchid, status_numeric )

and then rewrite queries and use the virtual column in them, for example:

From 
(
 select top(1) Cusip from cusiptbl 
 where batchid = -1 and status_numeric is not null
 order by status_numeric desc  
) Seconds

I do get deadlock issues if i use the Solution:02. Working towards an alternative solution. — nsivakr, Aug 15 '13 at 17:47
`ISNUMERIC` determines whether a string can be converted to *any* numeric type and thus is not reliable to use with regard to a *particular* type. For instance, `ISNUMERIC` would return 1 for `'.'` but that would fail to convert to a `decimal`. There are alternative methods, though, and some good suggestions can be found [here](http://stackoverflow.com/questions/312054/efficient-isnumeric-replacements-on-sql-server "Efficient ISNUMERIC() replacements on SQL Server?") and [here](http://stackoverflow.com/questions/17941035/try-convert-for-sql-server-2008-r2 "Try_Convert for SQL Server 2008 R2"). — Andriy M, Aug 16 '13 at 07:17

Retrieve and update the top row in a stored proc - accessed by multiple process

1 Answers1