What's wrong in this SQL Query? I am trying to update a column value
You never set your variable @Watermarkvalue to anything, so the value of the variable is NULL.

So this line:

Set @tabName ='SELECT '+ @watermarkValue +'= MAX([LOAD_DATE]) FROM '+ @tableName


Will always be NULL.

You want to fill your variable, so think you need something like this: (change to correct datatype)

ALTER PROCEDURE [dbo].[usp_UpdateWatermark] 
 @tableName nvarchar(500)

AS 
BEGIN

 DECLARE
 /* ============= Variables ============= */
  @watermarkValue  nvarchar(MAX)

  DECLARE
 /* ============= Variables ============= */
  @tabName  nvarchar(MAX)

  DECLARE @ParmDefinition nvarchar(500);
  SET @ParmDefinition = N'@watermarkValue int OUTPUT';

  SELECT @tabName = N'SELECT @watermarkValue = MAX(ID) FROM ' + @tablename;  

 Execute sp_executesql @tabName,@ParmDefinition,@watermarkValue=@watermarkValue OUTPUT;

 UPDATE dbo.WatermarkTable
 SET  WatermarkValue  = @watermarkValue 
 WHERE TableName = @tableName 
END