There must be a typo after @MAX_NUMBER:

SET @QUERY='SELECT @MAX_NUMBER = MAX('+@DB_NAME+'.'+@SCHEMA_NAME+'.'+@TABLE_NAME+'.'+@COLUMN_NAME+') FROM '+@DB_NAME+'.'+@SCHEMA_NAME+'.'+@TABLE_NAME

And, it's a good habit to prevent a possibility of an SQL injection:

SET @QUERY='SELECT @MAX_NUMBER = MAX('+TABLE_CATALOG+'.'+TABLE_SCHEMA+'.'+TABLE_NAME+'.'+COLUMN_NAME+') FROM '+TABLE_CATALOG+'.'+TABLE_SCHEMA+'.'+TABLE_NAME

FROM INFORMATION_SCHEMA.COLUMNS C

WHERE TABLE_CATALOG = @DB_NAME AND TABLE_SCHEMA = @SCHEMA_NAME AND TABLE_NAME = @TABLE_NAME AND COLUMN_NAME = @COLUMN_NAME