CODEWITHSUNDEEP
sqlt-sql
NealR
NealR

Reputation: 10669

ISNULL in SQL subquery not returning anything

Below is part of a SQL triggered that uses a subquery to return a value in the column TableString. The subquery will return the first two value and the "marker" for Fname, however nothing after that (not even the semicolon). The TableString column is an nvarchar that is set to 255 characters and captures all similar data during an insert or an update. 

INSERT INTO TransactionLog (TransactionDate, Operator, TableName, Action
                           , TableString, UserId)
SELECT LastChangeDate
     , 'Op'
     , @tableName
     , @action
     , CAST('sNum:' + CAST(sNumber as nvarchar(10)) + ' entType:' + EntityType 
            + ' Fname:' + ISNULL(FirstName, 'NULL') 
            + ' Lname:' + ISNULL(LastName, 'NULL') 
            + ' suff:' + ISNULL(NameSuffix, 'NULL') 
            + ' corpName:' + ISNULL(CorporateName, 'NULL' ) 
            + ' ctrlId:' + ISNULL(CAST(ControlId as nvarchar(3)), 'NULL') 
            AS nvarchar(30)) as TableString
     , LastChangeOperator
FROM deleted

Returned values in TableString:

sNum:1000024 entType:S Fname

Upvotes: 0

Views: 399

Answers (2)

Christopher
Christopher

Reputation: 1

As a side note, his return character count is 28 because Nvarchar(30) means, store 30 bytes and Unicode (N) is two bytes a letter, even if you are storing non-Unicode. The number in the varchar declare is not the number of characters, its the number of bytes to use. It really gets people when they use Nvarchar.

Upvotes: 0

user166390
user166390

Reputation:

This has nothing to do with ISNULL but rather, is the result of a truncation of data.

Pay attention to:

.. AS nvarchar(30)

That is, much more data is being omitted. (The output does not even contain "Fname:" in this case.)

Upvotes: 3

Related Questions