title: SQL Server 2008 - CAST to nvarchar not working
body: <p>I have the following SQL statement as part of a trigger that is not working and cant figure out why:</p>
  
  <pre><code>DECLARE @Orderid INT
  DECLARE @CountOfOrderItems INT
  DECLARE @EmployeeID INT
  DECLARE @EmployeeName NVARCHAR
  
  SELECT @Orderid = DELETED.id
  
  SET @CountOfOrderItems = (select count(*) from OrderDetails where OrderID = @Orderid)
  
  SET @EmployeeName = (SELECT Firstname + ' ' + Lastname from Employees where employees.id = 
                        (SELECT employeeid from Orders where Orders.id = @Orderid))
  
          INSERT INTO applicationlog (Sourceobject,Logtype,Message)
          VALUES ('Orders table',
                  'Order deleted',
                  'Order with ' + CAST(@CountOfOrderItems as varchar) + ' items deleted by '+ CAST(@EmployeeName as nvarchar))
  </code></pre>
  
  <p>The column datatypes for the applicationlog table are all <code>nvarchar(255)</code> for sourceobject and logtype and <code>nvarchar(max)</code> for message.</p>
  
  <p>The issue is, when I include the <code>CAST(@EmployeeName as nvarchar)</code> bit, nothing is entered in the message column, and it shows null. Data is entered in the other two columns. Casting the employeename as <code>varchar</code> also doesn't work.</p>
  
  <p>When I remove the bit shown in bold above and use the following code, it works and the casting for the countoforderitems works. </p>
  
  <pre><code>INSERT INTO applicationlog (Sourceobject,Logtype,Message)
  VALUES ('Orders table',
          'Order deleted',
          'Order with ' + CAST(@CountOfOrderItems as varchar) + ' items deleted')
  </code></pre>
  
  <p>I have no idea why and would appreciate some help, as I need to log the user that is deleting the record.</p>
  
  <p>Thanks</p>
  
  <p><strong>UPDATE</strong></p>
  
  <p>Thanks to all that have responded. I declared employeename as a <strong>VARCHAR(255)</strong> and edited the insert statement to</p>
  
  <pre><code>        INSERT INTO applicationlog (Sourceobject,Logtype,Message)
      values ('Orders table','Order deleted','Order with ' + CAST(@CountOfOrderItems as varchar(255)) + ' items deleted by ' + coalesce(@EmployeeName,''))
  </code></pre>
  
  <p>and now all information is there except the @Employeename which i think is returning an empty string, even though the employee exists in the table.So in the last column i now have <strong>"3 items deleted by "</strong></p>
  
  <p>Which is better than the null before, but why is the @Employeename blank?</p>