>>In a set of 1000 rows, I have a varchar column action_id which i need to cast to a smallint in another column in the row called spi_action_id
>>
>>( this is internal processing of an imported CSV file - action_id is taken as given from the CSV , spi_action_id is not part of CSV but stores the cast value as SMALLINT. This conversion is done on the rows after the SSIS has imported the CSV to SQL and is not part of the import itself )
>>
>>If for some reason the varchar will not cast to smallint in a particular row, I do not want to fail the entire set based operation but either set the spi_action_id to -1 and ideally to set the value of another column, row_status, to -9.
>>
>>This same logic would apply to the failure of any cast operation in a particular row. Is there a way to do this without resorting to procedural code?
>>
>>

>>Update #staging 
>>SET
>>eff_date = CAST(effective_date AS DATETIME),
>>spi_action_id = ISNULL(CAST(action_id AS SMALLINT),0 ),
>>spi_event_subtype_id = ISNULL(CAST(event_subtype_id AS SMALLINT),0) 
>>.....
>>

>>
>>We would basically be trapping for a case where the CSV gave us action_id = 'FRED' or effective_date = 'goodmorning'
>>
>>I guess I am talking about a try-catch of some sort on the casts of each row - and somehow doing it in a set-based way.
>>
>>I realize this is not the strength of set-based operations, but I thought there might be a trick to get it to work.
>>
>>TIA
>
>Strange, apparently I didn't post my first message to you about TRY_CAST method in SQL Server 2012 and also a where condition I made to filter bad rows, e.g.
>

>WHERE spi_action_id not LIKE '%[^-0-9]%' and LEN(spi_action_id) <=5

>-- this will filter bad rows although this is not 100% bullet proof condition to keep good rows only.
>
>--------
>I know why it didn't post the first and even second time - apparently the JavaScript validation message didn't show up or I somehow dismissed it and the message didn't post.

The LIKE expression idea is interesting and I could actually use that to filter the bad rows before I did the update using the casts on those that pass the test.

I have a row_status flag I can set on bad rows to keep them from further processing so testing for bad conditions and setting that flag first should make my update easier.

( Denis is on vacation on a beach someplace so I really appreciate the help :-) )