Store it in...

NullIf will be faster than a case statement by a small margin.

select cast(NullIf(datex, '00000000') as datetime)

Shouldn't matter regarding the sequence. The whole idea of Try/Catch is that if any error with a severity greater than 10 (I think that's the minimum) comes up, it...

peterhe (3/14/2008)

---

I found your loop and CTE are not equivalent. In the loop, you have the code

WHERE ...

and c2.structureid not in

...

It makes sense in that the transaction fails, so the catch is called.

select *

from dbo.rangeproof

where left(id, patindex('%[^0-9]%', id)-1) between '123' and '124'

Will get you the results you want, but it's going to be slow on a big table. (I tested it...

I must be missing something on this.

First, all of the inserts in your sample data have "N" in column "sku_upc", but you said you want <> "N", and your query...

If you can provide a copy of the create statements for the tables involved, and the code for the query, we can probably help you narrow down the exact problem.

GilaMonster (3/14/2008)

---

To do that, you'll need the previous day's...

I just tried this on two linked servers. The calling server is SQL 2005 (of course), one link is to SQL 2000, the other to SQL 2005.

create proc ErrorTest

as

declare...

I would tend to think that the problem is that it's running the called proc on SQL 2000, which doesn't have Try/Catch. But that's just a guess based on...

There are two ways I can think of to get that to work.

The first would be to insert the criteria into a temp table created in the calling procedure/script, then...

You should be able to do range searches on strings.

create table RangeProof (

ID varchar(25) primary key)

go

insert into dbo.rangeproof(id)

select '123-1234a' union all

select '124-1234a' union all

select '125-1234a' union all

select '123-1234b' union all

select...

I can't count the number of 3-day shifts and 4-day shifts I've done (as in no sleep for 3-4 days), and I've done quite a few 5-day shifts. But...

Another possible solution (if you can) is a computed column with the first character of the ID and an index on that. Or, if you can't modify the table,...