An expression of non-boolean type specified in a c

问题:

I have a script in trigger to insert the audit table if any update happens on T_Referral table and i am using a Decrypt function to Decrypt the data in one of the column. My code in trigger is :

DECLARE @Sql_Insert nvarchar(max)
SET @Sql_Insert = ''
SET @Sql_Insert='INSERT INTO [Logg].AuditLogData(TableName, ColumnName, OldValue, OldValue_Decode, NewValue, NewValue_Decode, AuditSubCategoryID,[GuID])
select TableName, ColumnName, OldValue_Decode, case when ColumnName = ''BookingUserReferenceValue'' then utl.sfDecrypt(NewValue,0) Else NewValue end, NewValue_Decode, AuditSubCategoryID,[GuID] from #AuditLogData
where ISNULL(OldValue,'') != ISNULL([NewValue],'')'

exec utl.uspOpenOrCloseEncryptionKey 'open'
exec(@Sql_Insert )
exec utl.uspOpenOrCloseEncryptionKey 'close'

---

My update script is

Update RTS.T_Referral   
set BookingUserReferenceValue =  cast ('John Wayne' as varbinary(256))
where ReferralId = 20

I am Updating the record to John Wayne as varbinary in T_Referral table(record looks like 0x4A6F686E205761796E65) and when update trigger is called it would load that record as John Wayne in Audit table. When the record is inserted in BookingUserReferenceValue it will be encrypted. The datatype of column BookingUserReferenceValue is Varbinary(256). when i try to update the record in that column i get error :

An expression of non-boolean type specified in a context where a condition is expected, near ')'.

Any Idea whats wrong? Thanks

回答1:

You need to escape the single quotes:

SET @Sql_Insert='
INSERT INTO [Logg].AuditLogData(TableName, ColumnName, OldValue, OldValue_Decode, NewValue, NewValue_Decode, AuditSubCategoryID,[GuID])
select 
    TableName, ColumnName, OldValue, OldValue_Decode, 
    case when ColumnName = ''BookingUserReferenceValue'' then utl.sfDecrypt(NewValue,0) Else NewValue end,
    NewValue_Decode, AuditSubCategoryID,[GuID] 
from #AuditLogData
where 
    ISNULL(OldValue, cast('''' as varbinary(256))) != 
    ISNULL([NewValue], cast('''' as varbinary(256)))
';

回答2:

If we write non-boolean expression in IF statement, WHERE clause, HAVING clause etc it is comes under syntax error. Please check your code.

回答3:

I think OldValue is missing from your SELECT list. I'm not sure why you didn't get the error that the number of columns didn't match.