I run the script and it freezes me but varchar(max) and nvarchar(max) -1 always the ones with throws. How max turns -1

Question

ALTER FUNCTION [dbo].[get_logtable_script]
    (@table_name varchar(max)) 
RETURNS varchar(max) 
AS BEGIN
   declare @sql varchar(5000)
   declare @column_name varchar(max), @data_type varchar(max), @maxlength varchar(5)

   set @sql = 'create table TEST_Log.dbo.log_'+@table_name+' (logid [int] IDENTITY(1,1) NOT NULL,log_time date, log_tip varchar(10)' 

   declare itable cursor LOCAL for 
        SELECT
            COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH 
        FROM
            INFORMATION_SCHEMA.COLUMNS 
        WHERE
            TABLE_NAME = @table_name
        ORDER BY
            ORDINAL_POSITION

OPEN itable;
FETCH NEXT FROM itable INTO @column_name, @data_type, @maxlength

WHILE @@FETCH_STATUS = 0
BEGIN
    set SET @sql = @sql + ','+@column_name+' '+@data_type+case when isnull(@maxlength,'')= '' then '' else '('+ @maxlength + ')' end

    FETCH NEXT FROM itable INTO @column_name, @data_type, @maxlength
END

SET @sql = @sql + ')'   
CLOSE itable
DEALLOCATE itable

RETURN @sql
END

Answer 1

CHARACTER_MAXIMUM_LENGTH is -1 for MAX so your output string is varchar(-1) which is not correct, use:

set @sql = @sql + ','+ quotename(@column_name) + ' ' + @data_type +
           case when @maxlength is null then '' else 
               '('+ case when @maxlength = -1 then 'max' else @maxlength end + ')' 
           end

Note the addition of quotename to handle reserved/spaced column names. This approach wont work for types with a scale/precision.