title: Seeing String or binary data would be truncated when executing Stored Procedure MS SQL 2008
body: <p>I have a stored procedure in MS SQL 2008, which compiles without error, but when go to execute the stored procedure I receive the error message 'String or binary data would be truncated'. Unfortunately, I am unable to trace where this is happening.</p>
  
  <p>When I developed my Stored Procedure, I tested the query first, and it worked.</p>
  
  <p>So, if anyone can show where and why this happening to me and how to solve it, I would be grateful:</p>
  
  <pre><code>ALTER PROCEDURE [dbo].[sp_Material_Validation]
  
  @ValidType varchar(MAX),
  @MaterialID varchar(MAX)
  AS
  BEGIN
  
  
  DECLARE
  @MessageReturn varchar(100);
  SET @MessageReturn = NULL;
  
  IF @ValidType = 'Primary'
  
  Select TOP 1 Valid.StatusMessage
  From
  (Select MatID,'Property Not Completed' as StatusMessage
  From dbo.Properties
  WHERE DATALENGTH(ValueString)=0
  UNION
  Select PrpCount.MatID,'There not all Properties exist' as StatusMessage
  From (Select Count(unkey) as unKeyCt, MatID
  From Properties
  Group By MatID
  Having NOT Count(Cast(UnKey as Int)) = 19)AS PrpCount
  UNION
  SELECT Header,'Material does not exist' as StatusMessage
  FROM Materials Item
  Where NOT EXISTS (Select ID
  FROM MatMaster Mat
  WHERE MAT.ID= Item.HEAD)
  UNION
  SELECT Header,'Material(s) not Complete' as StatusMessage
  FROM Materials
  Where MatID IN (Select ID
  FROM MatMaster
  WHERE INC ='Y')) as Valid
  Where Valid.MatID = @MaterialID;
  
  IF @MessageReturn IS NOT NULL
  
  UPDATE dbo.Mat_DEV
  SET Inco ='Y',
  User_Message = @MessageReturn
  WHERE MatID = @MaterialID
  
  ELSE
  UPDATE dbo.Mat_DEV
  SET User_Message = 'No Validation Errors'
  WHERE MatID = @MaterialID
  </code></pre>