Change Column datatype

BEGIN

/*Create the table to collect the column details and status*/IF OBJECT_ID (N'ColumnDataTypeChange', N'U') IS NOT NULL
BEGIN
Truncate Table ColumnDataTypeChange
END
Else
BEGIN
Create Table ColumnDataTypeChange
(
ID int identity(1,1) Primary Key,
TableName nVarchar(1000),
ColumnName nVarchar(1000),
DataType nVarchar(1000),
ColumnSize int,
isUpdated bit,
ErrorMsg  nVarchar(1000),
SQL_Script nVarchar(2000)
)
END
/*Fetch the details of columns*/Insert into ColumnDataTypeChange
SELECT distinct O.name as 'TableName',C.name as 'ColumnName',t.name as 'DataType',C.max_length as 'ColumnSize',
 0 as isUpdated, NULL as ErrorMsg,NULL as SQL_Script
FROM   
    sys.columns c
INNER JOIN
    sys.types t ON c.user_type_id = t.user_type_id
Inner Join sys.objects O on O.object_id=C.object_id and O.type='U'
WHERE T.name='varchar' or T.name='char' or T.name='text'

Declare @TotalRec int
Declare @Count int
Declare @TableName nVarchar(1000)
Declare @ColumnName nVarchar(1000)
Declare @DataType nVarchar(1000)
Declare @ColumnSize int
Declare @SQL nVarchar(1000)
Declare @ErrorMsg nVarchar(1000)

SET @Count=1
SET @TotalRec=(SELECT COUNT(*) FROM ColumnDataTypeChange T)
/*Generate Alter statement and execute them*/While @Count<=@TotalRec
BEGIN

BEGIN TRY
SET @TableName =(SELECT TOP 1 T.TableName FROM ColumnDataTypeChange T WHERE T.ID=@Count )
SET @ColumnName =(SELECT TOP 1 T.ColumnName FROM ColumnDataTypeChange T WHERE T.ID=@Count )
SET @DataType =(SELECT TOP 1 T.DataType FROM ColumnDataTypeChange T WHERE T.ID=@Count )
SET @ColumnSize =(SELECT TOP 1 T.ColumnSize FROM ColumnDataTypeChange T WHERE T.ID=@Count )

SET @SQL='ALTER TABLE dbo.'+@TableName+' ALTER COLUMN '+'['+@ColumnName+']'+' '+'n'+@DataType+'('+CONVERT(nvarchar,@ColumnSize)+')'

EXEC(@SQL)

      Update ColumnDataTypeChange
      SET isUpdated=1
      WHERE ID=@Count

END TRY

BEGIN CATCH 
    /*Capture the error details in case of failure*/ 
      SET @ErrorMsg=(SELECT ERROR_MESSAGE() AS ErrorMessage)

      Update ColumnDataTypeChange
      SET isUpdated=0
      , ErrorMsg= @ErrorMsg
      ,SQL_Script=@SQL
      WHERE ID=@Count
    
END CATCH;


SET @Count=@Count+1
END-- While ENDs

END