UPDATE Extended Stored Procedures

These stored procedures are used while protecting data using Update statements.

xp_pty_update

This stored procedure protects the data using an encryption data element.

Signature:

xp_pty_update(outputdata VARBINARY OUTPUT, data VARCHAR, dataelement VARCHAR, status CHAR, scid INT)

Parameters:

Returns:
This UDF returns either of the following values as protected data in the output parameter:

Exception:
If a data element or a security operation is configured in the policy and the user does not have access, then the function will terminate with an appropriate error message.

Example:

DECLARE
 @result INT,
 @outputdata VARBINARY(8000),
 @data VARCHAR(8000)
SET @data = '232432423432'
EXEC @result = <database_name>.dbo.xp_pty_update @outputdata output, @data, 'AES256', 'T', 0
IF @result = 0
 PRINT 'OK'
ELSE
 PRINT 'ERROR'
PRINT @outputdata

In the Example, value=‘AES256’, is the name of the data element.

xp_pty_tpe_unicode_update

This stored procedure protects the data using the FPE Unicode data element only.

Note: This UDF does not support masking.

Signature:

xp_pty_tpe_unicode_update(outputdata NVARCHAR OUTPUT, data NVARCHAR, dataelement VARCHAR, status CHAR, scid INT)

Parameters:

Returns:
This UDF returns either of the following values as protected data in the output parameter:

Exception:
If a data element or a security operation is configured in the policy and the user does not have access, then the function will terminate with an appropriate error message.

Example:

DECLARE
 @result INT,
 @outputdata NVARCHAR(4000),
 @data NVARCHAR(4000)
SET @data = N'232432423432'
EXEC @result = <database_name>.dbo.xp_pty_tpe_unicode_update @outputdata output, @data, 
'fpe_unicode', 'T', 0
IF @result = 0
 PRINT 'OK'
ELSE
 PRINT 'ERROR'
 PRINT @outputdata

xp_pty_tpe_update

This stored procedure protects the data using type-preserving data element, such as Tokens and No Encryption, for access control.

Signature:

xp_pty_tpe_update(outputdata VARCHAR OUTPUT, data VARCHAR, dataelement VARCHAR, status CHAR, scid INT)

Parameters:

Returns:
This UDF returns either of the following values as protected data in the output parameter:

Exception:
If a data element or a security operation is configured in the policy and the user does not have access, then the function will terminate with an appropriate error message.

Example:

DECLARE
 @result INT,
 @outputdata VARCHAR(8000),
 @data VARCHAR(8000)
SET @data = 'How are you'
EXEC @result = <database_name>.dbo.xp_pty_tpe_update @outputdata output, @data, 
'TE_A_S13_L0R0_Y', 'T', 0
IF @result = 0
 PRINT 'OK'
ELSE
 PRINT 'ERROR'
PRINT @outputdata

xp_pty_tpe_int_update

This stored procedure protects the input data (integer) using an integer-tokenization data element.

Signature:

xp_pty_tpe_int_update(outputdata INT OUTPUT, data INT, dataelement VARCHAR, scid INT)

Parameters:

Returns:
This UDF returns either of the following values as protected data in the output parameter:

Exception:
If a data element or a security operation is configured in the policy and the user does not have access, then the function will terminate with an appropriate error message.

Example:

DECLARE 
 @result INT,
 @outputdata INT,
 @data INT
SET @data ='1234'
EXEC @result= <database_name>.dbo.xp_pty_tpe_int_update @outputdata output, @data, 'Integer', 0
IF @result = 0
 PRINT 'OK'
ELSE 
 PRINT 'ERROR'
PRINT @outputdata

xp_pty_update_hash

This stored procedure calculates the hash value of the input data using a HMAC_SHA256 data element.

Note:
New data elements under HMAC_SHA1 cannot be created.
Existing data elements under HMAC_SHA1 remain supported and can continue to be used.

^*HMAC_SHA1 is deprecated. We recommend transitioning to HMAC_SHA256, which continues to be supported.

Signature:

xp_pty_update_hash(hash VARBINARY OUTPUT, data VARCHAR, dataelement VARCHAR, scid INT)

Parameters:

Returns:
This UDF returns either of the following values as a result of the hash operation:

Exception:
If a data element or a security operation is configured in the policy and the user does not have access, then the function will terminate with an appropriate error message.

Example:

DECLARE 
 @result INT,
 @hash VARBINARY(64),
 @data VARCHAR(64)
SET @data='232432423432'
EXEC @result= <database_name>.dbo.xp_pty_update_hash @hash output,@data, 'HMAC_SHA256', 0
IF @result=0
 PRINT 'OK'
ELSE 
 PRINT 'ERROR'
PRINT @hash