Assigning Always Encrypted Data to a TSQL variable

• Dave Zahn

  Say Hey Kid

  Points: 681

  More actions

  June 6, 2018 at 1:59 pm

  #354759

  We have an application that needs to occasionally fix bad data on Always Encrypted columns.  Because of a variety of reasons, they can't totally clean data until it arrives in their system.  The team is working on an application-level solution; in the meantime, the DBAs are fixing the data in SSMS.  Parameterized queries are working fine for simple inserts and updates. 

  Occasionally the application team has a more complex request, such as replacing XXXXX with YYYYY in a single row's encrypted VARCHAR(MAX) field.  

  The requirement to assign the value in the DECLARE statement is the source of the issue.  Unless I am missing something obvious (entirely possible) I can assign static values to a variable in a DECLARE statement, but can't assign the results of a SELECT statement.  The plan had been to pull the single value out in a SELECT statement, run a replace, and then insert the new value via a parameterized query.  

  My guess is that we are out of luck, and will first have to extract the unencrypted value, and then hardcode the new value in a DECLARE statement - but I wanted to put the question out there in case someone has a better interim solution for us.

  Thanks!