You mean the second example I posted didn't work? (The first example was how NOT to do it)...

Recommend you post the actual trigger code and a bit of information on the schema of the tables...

That's because it looks like you're trying to add spaces to a numeric value which has no effect.  Convert the street number to VARCHAR and then add the spaces to...

Sorry, Joe... I misread the post quite badly... thought you were trying to do locked reads.

I realize this is a really old thread, but I read it with some great interest... not one of the folks that claimed that case sensitivity created that mythical 40%...

That requires a change in "COLLATION"... unfortunately, there's a bug in SQL Server that destroys the ability to use collation where a range of letters is included in a LIKE......

Then, the trigger is wrong... it should be able to handle both.

Allen,

First, there is no reason, whatsoever, to store the "M" in the sequence table.  All it does is slow everything down with unneccessary character conversions.

Using the good example that Mr....

Depending on how and where the function is used in a WHERE clause, it can drastically and negatively affect performance because it may disable the ability for INDEX SEEKs to...

That's a bit, well, insane... why is the trigger there in the first place if you're going to just disable it?  Either the trigger is bad or your code is...

IF you're going to use an undocumented sproc, why not go all the way and get ALL the filenames at once?

 CREATE TABLE #Directory (RowNum INT IDENTITY(1,1),FileName SYSNAME,Depth...

Unless they are set based procs... "Death by SQL"... the idea of code reuse to process single records will absolutely kill performance and make more maintanence nightmares than you can...

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

First question would be... Why aren't you using an IDENTITY column to do this along with Scope_Identity.

Next question would be... Please post your SP...