>>>>>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
>>>>
>>>>You need to filter bad rows using WHERE clause. Alternatively you can use SSIS. There is no other methods to filter bad rows but process good ones.
>>>
>>>I don't think i explained it well. i don't want to filter them, I want to prevent a bad cast in one row from stopping the entire update.
>>>
>>>I get the impression from what you say this is not possible. What do you mean by a WHERE clause? I don't know which rows will not cast until I try to cast them.
>>>
>>>How could I use a where clause here?
>>
>>In SQL Server 2012 You can use TRY_CAST to attempt to cast into the proper type. If it returns NULL, it can not be cast to the desired type.
>
>That is indeed what I am looking for, but can you do it in line on a row the way we would a CASE statement?
>
>In any case, this is SQL2008 R@ so I think I will go with the option of trying to filter out the row most likely to contain stuff that won't cast ( alpha where there should be only digits etc ) and going from there. Fortunately this data is not very dirty so any mis-cast is very very edge case ( trying to satisfy the fear of QA people who do not understand what we are doing well enough to prioritize the likelihood of any given scenario )

I mistyped, the function is try_convert
http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/MSSQLServer/try_convert-in-sql-server-denali