Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
Catching bad casts in set based operation
Message
 
 
À
22/08/2012 12:02:04
Information générale
Forum:
Microsoft SQL Server
Catégorie:
Syntaxe SQL
Versions des environnements
SQL Server:
SQL Server 2008
Divers
Thread ID:
01550993
Message ID:
01551038
Vues:
62
>>>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.
If it's not broken, fix it until it is.


My Blog
Précédent
Suivant
Répondre
Fil
Voir

Click here to load this message in the networking platform